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

shiyiming

方法一：（此方法效率方面可能不是最优）
data tmpTable(drop=i);
        do i=1 to 12;
                do cid=1 to 2;
                        month="0";
                        balance=.;
                        output;
                end;
        end;
run;
data tmpTable;
        set a tmpTable;
run;
proc sort data=tmpTable;
        by cid month;
run;
data resultTable;
        set tmpTable;
        array a[*] b1-b12;
        a[1]=lag(balance);
                do i=2 to 12;
                     a[i]=lag(a[i-1]);
        end;
        if month="0" then delete;
        mean6=mean(of b1-b6);
        mean12=mean(of b1-b12);
        drop b1-b12 i;
run;

shiyiming

傻办法，创建12张表作left join。就是冗余太厉害。

shiyiming

data test(keep=cid month mean6 mean12);
set a;
by cid;
array lag_baln(12) lag_baln1-lag_baln12;
retain lag_baln(12);
do i=12 to 2 by -1;
   lag_baln(i)=lag_baln(i-1);
end;
lag_baln(1)=lag(balance);
if first.cid then
do i=1 to 12;
  lag_baln(i)=.;
end;
mean6=mean(of lag_baln1-lag_baln6);
mean12=mean(of lag_baln1-lag_baln12);
run;
proc print data=test;
run;

shiyiming

data u1 u2;
set a;
if cid = 1 then output u1;
if cid = 2 then output u2;
run;
%MACRO Blag(user);
set u&user;
%do i=1 to 12;
b&i=lag&i(balance);
%end;
%MEND;
data b1(keep = cid month balance mean6 mean12);
set u1;
by cid;
%Blag(1);
mean6 = mean (of b1-b6);
mean12 = mean (of b1-b12);
run;
data b2(keep = cid month balance mean6 mean12);
set u2;
by cid;
%Blag(2);
mean6 = mean (of b1-b6);
mean12 = mean (of b1-b12);
run;
data result;
   set b1 b2;
run;
proc print data = result;
run;
这道题采用分治id1和id2，然后把处理结果再合并的方法最不费脑子，但是实际用户较多的话可能会在性能方面打折扣。

shiyiming

我的答案 <!-- s:D --><img src="{SMILIES_PATH}/icon_biggrin.gif" alt=":D" title="Very Happy" /><!-- s:D -->  <!-- s:lol: --><img src="{SMILIES_PATH}/icon_lol.gif" alt=":lol:" title="Laughing" /><!-- s:lol: -->
data a;
infile datalines;
input cid  month $ balance;
datalines;
1 200001 1
1 200002 1
1 200003 2
1 200004 .
1 200005 1
1 200006 1
1 200007 1
1 200008 1
1 200009 1
1 200010 1
1 200011 1
1 200012 1
1 200101 1
1 200102 1
1 200103 1
1 200104 1
1 200105 1
1 200106 1
1 200107 1
1 200108 1
1 200109 1
2 200001 1
2 200002 1
2 200003 2
2 200004 .
2 200005 1
2 200006 1
2 200007 1
2 200008 1
2 200009 1
;
data result(keep=x1 x2);
set a;
by cid  ;
retain total1 0;
retain total2 0;
retain totalmiss 0;
retain miss1 0;
retain miss2 0;
if first.cid  then do; totalmiss=0; total1=0; total2=0 ;count=0;miss1=0;miss2=0;end;
count+1;
if balance&gt;0 then  miss=1;else  miss=0;
if miss1&gt;0 then x1=total1/miss1 ;
if miss2&gt;0 then x2=total2/miss2;
totalmiss=totalmiss+miss;
lag6=lag6(totalmiss);
lag12=lag12(totalmiss);
lag66=lag6(balance);
lag1212=lag12(balance);
if balance&gt;0 then do;

if count gt 6 then do;
total1=sum(total1,balance,-lag66) ;
miss1=sum(totalmiss,-lag6);end;
else  do; total1=total1+balance;miss1=totalmiss; end;
if count gt 12 then do;
total2=sum(total2,balance,-lag1212);
miss2=sum(totalmiss,-lag12); end;
else  do ; miss2=totalmiss; total2=total2+balance;end;
end;
else do;

if count gt 6 then do;
total1=sum(total1,-lag66) ;
miss1=sum(totalmiss,-lag6);end;
else  do ;miss1=totalmiss; end;
if count gt 12 then do;
total2=sum(total2,-lag1212);
miss2=sum(totalmiss,-lag12); end;
else  do ;miss2=totalmiss;end;
end;

run;
proc print data=result;
run;

shiyiming

通过最近努力学习，发现将retain和lag结合计算移动平均，效率最高，程序也容易理解，缺点是会损失一行数据。
proc sort data = a;
by cid;
run;

data a1;
set a;
retain sum_balance 0;
sum_balance=sum_balance+balance;
mean6=(sum_balance-lag6(sum_balance))/6;
mean12=(sum_balance-lag12(sum_balance))/12;
run;

shiyiming

[code:thmsu4t3]proc sort data=a;
     by cid month;
run;

data a;
     set a;
         by cid month;
         if first&#46;cid then num=0;
         num+1;
run;

data avg1;
     input avg1 8&#46;3;
         cards;
run;

data avg2;
     input avg2 8&#46;3;
         cards;
run;

data aa;
     set a;
         call execute(&quot;data temp;
                   set a; where cid=&quot;||cid||&quot; and num&lt;&quot;||num||&quot; and num&gt;=&quot;||num||&quot;-6;
                   run;
                   proc means data=temp noprint;
                                        var balance; output out=tmp mean=avg1; run;
                   data avg1; set avg1 tmp;  run;&quot;);
run;

data aa;
     set a;
         call execute(&quot;data temp;
                   set a; where cid=&quot;||cid||&quot; and num&lt;&quot;||num||&quot; and num&gt;=&quot;||num||&quot;-12;
                   run;
                   proc means data=temp noprint;
                                        var balance; output out=tmp mean=avg2; run;
                   data avg2; set avg2 tmp;  run;&quot;);
run;

data avg;
     set a;
         by cid month;
         if first&#46;cid then delete;
run;

data avg;
     merge avg avg1 avg2;
         label avg1='6 month mean'
               avg2='12 month mean';
        keep cid month avg1 avg2;
run;

data final;
     merge a avg;
         by cid month;
         drop num;
run;[/code:thmsu4t3]

shiyiming

很有趣，这个是我常常问的面试题，不过不止计算移动平均这么简单。

2008年的帖子，居然今天才看到，算是挖坟行为 <!-- s8) --><img src="{SMILIES_PATH}/icon_cool.gif" alt="8)" title="Cool" /><!-- s8) -->

我面试的时候常常问：从1--10，10算最好， 你自己觉得自己的SAS编程水平几分？或者是不是常常用SAS做时间序列分析？凡是回答是，或者答5-6的我就问这种移动平均，答7-8的我就问移动相关系数，答9以上的我就问以变量值为基准的移动窗口统计量计算。目前为止遇到的能答上来的回答都跟大家一样，硬算！

[code:bwp97i7i]
data a;
infile datalines;
input cid month yymmn6&#46; balance;
format month yymm&#46;;
datalines;
1 200001 1
1 200002 1
1 200003 2
1 200004 &#46;
1 200005 1
1 200006 1
1 200007 1
1 200008 1
1 200009 1
1 200010 1
1 200011 1
1 200012 1
1 200101 1
1 200102 1
1 200103 1
1 200104 1
1 200105 1
1 200106 1
1 200107 1
1 200108 1
1 200109 1
2 200001 1
2 200002 1
2 200003 2
2 200004 &#46;
2 200005 1
2 200006 1
2 200007 1
2 200008 1
2 200009 1
;
run;


data av/view=av;
        set a; by cid;
        b_lag=lag(balance);
        if first&#46;cid then b_lag=&#46;;               
run;

proc expand data=av   out=b  method=none;
       by cid;          
       convert b_lag=mean6 / transformout=(movave 6  );
       convert b_lag=mean12/transformout=(movave 12 );
run;

data _null_;
        set b;
                put @1 cid= @10 month=  @25 balance=  @40 mean6= 7&#46;5  @60 mean12= 7&#46;5;;
run;
[/code:bwp97i7i]

shiyiming

我通常只敢说自己的SAS Programming 3-4分，只能应付工作需求。很多人都自信满满的回答我：“我在我们组里就是最强的，可以打9分吧！”。估计是谦虚了一下，没说满10分。结果问了2个问题，再让他给自己打分，“嗯~~，不好意思，我想只能算5分吧”。其实还高了。 <!-- s:D --><img src="{SMILIES_PATH}/icon_biggrin.gif" alt=":D" title="Very Happy" /><!-- s:D -->

shiyiming

你老人家都只有3--4，一般的岂不是只能给自己打一分？还要不要人家活了