如何在SQL中创建随分组变量重置的移动平均及SAS Proc Expand问题解决

数据集说明 #

给定的示例数据集按cat1（数值型分组）、cat2（字符型分组）划分组，每个组内有连续的seq序列（1到20），对应value值。需求是计算分组内重置的滚动平均，同时解决Proc Expand的两个问题。

方法一：Proc Expand 实现分组滚动平均 #

核心问题解决 #

1. 按序列（类日期）组织变量：将seq作为时间ID变量，让Proc Expand识别为序列维度
2. 分组重置计算：通过BY语句指定分组变量，使每个分组独立计算滚动平均

代码示例（以3期滚动平均为例） #

/* 先确保数据集按分组+序列排序 */
data have_sorted;
  set have;
  by cat1 cat2 seq;
run;

/* 使用Proc Expand计算分组滚动平均 */
proc expand data=have_sorted out=want_expand method=none;
  by cat1 cat2; /* 按分组变量拆分，分组变化时重置计算 */
  id seq; /* 将seq作为时间ID，模拟日期序列的组织方式，实际日期变量可直接替换 */
  convert value = value_rollavg / transformout=(movavg 3); /* 3期滚动平均，可调整窗口大小 */
run;

代码说明 #

• by cat1 cat2：强制Proc Expand在每个分组内单独处理，分组切换时自动重置滚动窗口
• id seq：指定seq为时间序列标识，满足“按日期组织变量”的需求
• transformout=(movavg N)：N为滚动窗口的期数，比如movavg 5就是5期滚动平均

方法二：SAS SQL 实现分组滚动平均 #

利用SAS SQL的窗口函数，通过PARTITION BY指定分组，ORDER BY指定序列顺序，结合AVG()窗口函数实现分组内重置的滚动平均。

代码示例（3期滚动平均） #

proc sql;
  create table want_sql as
  select 
    cat1,
    cat2,
    seq,
    value,
    /* 计算当前行及前2行的滚动平均（共3期），分组内自动重置 */
    avg(value) over(partition by cat1, cat2 order by seq rows between 2 preceding and current row) as value_rollavg
  from have
  order by cat1, cat2, seq;
quit;

代码说明 #

• partition by cat1, cat2：按分组变量划分计算窗口，每个分组独立运算
• order by seq：确保窗口内按序列顺序计算
• rows between 2 preceding and current row：定义滚动窗口范围，2 preceding表示当前行的前2行，加上当前行共3期；若需包含后续行的滚动平均，可调整为rows between current row and 2 following
• 若需要从第一行到当前行的累积滚动平均，可简化为avg(value) over(partition by cat1, cat2 order by seq)

结果验证 #

两种方法生成的value_rollavg结果一致：每个cat1+cat2分组内，seq=1和seq=2时因窗口不足3期，会计算已有行的平均（比如seq=1时为自身值，seq=2时为前两行的平均）；从seq=3开始为完整3期平均，分组切换时自动重置计算。

内容的提问来源于stack exchange，提问作者ndruiz