练手系列2:移动平均计算

shiyiming

学习中～ 回家再修改，争取过关，呵呵

shiyiming

proc sort data = a;
by cid;
run;
data b;
set a;
cnt + 1;
by cid;
if first.cid then cnt = 1;
array lagb(12) lagb1 - lagb12;
i=1;
lagb1=lag(balance);
if(cnt=1)then lagb1=.;

do until (i>11);
i+1;
lagb(i)=lag(lagb(i-1));
end;

j=12;
do until (j=0);
if(cnt<j) then lagb(j)=.;
j=j-1;
end;
mean6=mean(of lagb1-lagb6);
mean12=mean(of lagb1 - lagb12);
keep cid month balance mean6 mean12;
run;

shiyiming

再简化的话就可以不用j,直接用i就可以了.

shiyiming

强的！恭喜过关！ <!-- s:D --><img src="{SMILIES_PATH}/icon_biggrin.gif" alt=":D" title="Very Happy" /><!-- s:D -->

shiyiming

[code:bf694]
proc delete data=_all_;run;
proc sort data=a;by cid;run;
%macro tt(g);
data b&amp;g;
set a(where=(cid=&amp;g));

lagb1=lag(balance);
lagb2 =lag2 (balance);
lagb3 =lag3 (balance);
lagb4 =lag4 (balance);
lagb5 =lag5 (balance);
lagb6 =lag6 (balance);
lagb7 =lag7 (balance);
lagb8 =lag8 (balance);
lagb9 =lag9 (balance);
lagb10=lag10(balance);
lagb11=lag11(balance);
lagb12=lag12(balance);

mean6=mean(of lagb1-lagb6);
mean12=mean(of lagb1 - lagb12);

keep cid month balance mean6 mean12;
run;

%mend;

data _null_;
set a;by cid;
if first&#46;cid then call execute('%tt('||cid||')');
run;

proc sql;
select count(distinct cid) into &#58;cnt from a;
quit;

%macro test;
%do j=1 %to &amp;cnt;
proc append base=b0 data=b&amp;j force;
run;
%end;
%mend;
%test
proc print data=b0;run;[/code:bf694]

shiyiming

比较复杂，看的不太懂，楼上的TX是把每个客户的数据单独处理后在merge一起的？

shiyiming

waterlwh的程序通俗易懂，就是每个客户单独处理，最后把结果摞起来。这样很容易维护，唯一的不足就是大数据量性能不佳，不可实际用于生产环境。还是要赞一个！
xsmile的答案我觉得基本是正解了。

我的参考程序供大家一起讨论，不知道还没有高手再改进一下：
[code:0d6e2]%let period=24;
data b(drop=i lastn1-lastn&amp;period);
set a;
retain lastn1-lastn&amp;period;
by cid;
array lastn{&amp;period};
do i=&amp;period to 2 by -1;
  lastn{i}=lastn{i-1};
end;
lastn{1}=lag(balance);
if first&#46;cid then do i=1 to &amp;period;
        lastn{i}=&#46;;
end;
mean6=mean(of lastn1-lastn6);
mean12=mean(of lastn1-lastn12);
mean24=mean(of lastn1-lastn24);
run;[/code:0d6e2]

shiyiming

简练~ 学习一下,还有其他练习题目吗?

shiyiming

如何获取下一行的数据呢，与lag功能相反？

shiyiming

这个问题简单了点，呵呵，用行号降序排列再用lag就是了