时间戳

首页 > 解决方案 > 回顾 SAS

sas - 回顾 SAS

问题描述

我有一个按产品按周排序的产品销售和定价数据集。我想创建一个数据步骤，从当前周开始“回顾”12 周并选择该产品的最高价格。随着数据步骤的进展，12 周的“回顾”期将向前推进。

这可能吗？

另外，我不是 sas 编码器。简单的数据步骤是我的速度。

我也是这里的新手，不知道如何发布数据，所以我可以使用快速指针来说明如何做到这一点，我会更新我的帖子。

谢谢

杰夫

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

标签： sas

解决方案

一种方法是使用自反子选择的 SQL 查询来计算滑动窗口查找。

* 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
  ;

第二种方法是对数据进行串行处理并使用环形（或圆形）数组来存储过去的值。环形数组引用使用索引模数来确保循环性。最大值是从环形数组中计算出来的，当出现新项目时，数组会被重置。 

  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;

推荐阅读