在 SAS 中回顾 [英] Looking back in SAS
问题描述
我有一个按产品按周排序的产品销售和定价数据集.我想创建一个数据步骤，从当前周回顾"12 周并选择该产品的最高价格.然后，随着数据步骤的进展，12 周的回顾"期将向前推进.
I have a data set of product sales and pricing that is sorted by product by week. I want to create a data step that "looks back" 12 weeks from the current week and selects the maximum price for that product. The 12 week "look back" period would then move forward as the data step progresses.
这可能吗?
另外，我不是SAS编码员.简单的数据步骤就是我的速度.
Also, i'm NOT a sas coder. Simple data steps are my speed.
我也是这里的新手，不知道如何发布数据，所以我可以使用快速指针来说明如何发布数据，然后我会更新我的帖子.
I'm also a newbie here and do not know how to post data so I could use a quick pointer on how to do that and I'll update my post.
谢谢
杰夫
Item Week Units Dollars Avg Price
Item 1 2505 14 $315 $22.50
Item 1 2506 7 $166 $23.71
Item 1 2507 7 $100 $14.36
Item 1 2508 13 $387 $29.77
Item 1 2509 11 $231 $21.00
Item 1 2510 7 $168 $24.00
Item 1 2511 15 $397 $26.47
Item 1 2512 12 $222 $18.50
Item 1 2513 14 $453 $32.36
Item 1 2514 19 $557 $29.32
Item 1 2515 12 $369 $30.73
Item 1 2516 11 $272 $24.73
Item 1 2517 15 $462 $30.80
Item 1 2518 9 $160 $17.78
Item 1 2519 15 $404 $26.93
Item 1 2520 17 $382 $22.47
Item 1 2521 4 $129 $32.25
Item 1 2522 9 $219 $24.33
Item 1 2523 8 $274 $34.22
Item 1 2524 30 $685 $22.83
Item 1 2525 25 $607 $24.28
Item 1 2526 15 $430 $28.67
Item 1 2527 19 $445 $23.42
Item 1 2528 11 $295 $26.81
Item 1 2529 14 $356 $25.43
Item 1 2530 17 $396 $23.32
Item 1 2531 13 $340 $26.15
Item 1 2532 13 $329 $25.31
Item 1 2533 8 $240 $30.00
Item 1 2534 10 $230 $23.00
Item 1 2535 6 $268 $44.67
推荐答案
一种方法是使用 SQL 查询和自反子选择来计算滑动窗口查找.
One approach is a SQL query with a reflexive sub-select for computing sliding window lookups.
* The & in list input means the values are separated by two or more whitespace;
data have;
input
Item& $ Week& Units& Dollars& dollar4. Avg_Price& dollar7.2;
format avg_price dollar6.2;
datalines;
Item 1 2505 14 $315 $22.50
Item 1 2506 7 $166 $23.71
Item 1 2507 7 $100 $14.36
Item 1 2508 13 $387 $29.77
Item 1 2509 11 $231 $21.00
Item 1 2510 7 $168 $24.00
Item 1 2511 15 $397 $26.47
Item 1 2512 12 $222 $18.50
Item 1 2513 14 $453 $32.36
Item 1 2514 19 $557 $29.32
Item 1 2515 12 $369 $30.73
Item 1 2516 11 $272 $24.73
Item 1 2517 15 $462 $30.80
Item 1 2518 9 $160 $17.78
Item 1 2519 15 $404 $26.93
Item 1 2520 17 $382 $22.47
Item 1 2521 4 $129 $32.25
Item 1 2522 9 $219 $24.33
Item 1 2523 8 $274 $34.22
Item 1 2524 30 $685 $22.83
Item 1 2525 25 $607 $24.28
Item 1 2526 15 $430 $28.67
Item 1 2527 19 $445 $23.42
Item 1 2528 11 $295 $26.81
Item 1 2529 14 $356 $25.43
Item 1 2530 17 $396 $23.32
Item 1 2531 13 $340 $26.15
Item 1 2532 13 $329 $25.31
Item 1 2533 8 $240 $30.00
Item 1 2534 10 $230 $23.00
Item 1 2535 6 $268 $44.67
run;
proc sql;
create table want as
select
outer.*,
(select max(inner.avg_price) from have as inner
where inner.week between outer.week-12 and outer.week-1
and outer.item = inner.item
) as item_max_avg_price_12wk_prior format=dollar6.2
from
have as outer
order by
week
;
第二种方法是串行处理数据并使用环形(或圆形)数组来存储过去的值.环形数组引用使用索引模数来确保圆度.最大值是根据环形数组计算的，当出现新项目时，该数组会被重置.
A second approach is serial processing of the data and using a ring (or circular) array to store past values. A ring array reference uses a modulus of index to ensure circularity. The max is computed from the ring array and the array is reset when a new item appears.
data want;
array prices (0:11) _temporary_; * ring array, index is addressed in modulo;
set have;
by item;
if first.item then do;
call missing (of prices(*));
ringdex = 0;
end;
format item_max_avg_price_12wk_prior dollar6.2;
item_max_avg_price_12wk_prior = max (of prices(*));
* log ring array if interested;
* put item_max_avg_price_12wk_prior @;
* do _n_ =lbound(prices) to hbound(prices); put prices(_n_) 6.2 @; end; put;
prices(mod(ringdex,12)) = avg_price; * modulo index <==> ring;
ringdex++1;
run;
这篇关于在 SAS 中回顾的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
