我的参考程序供大家一起讨论，不知道还没有高手再改进一下：
[code:0d6e2]%let period=24;
data b(drop=i lastn1-lastn&period);
set a;
retain lastn1-lastn.
by cid;
array lastn{&period};
do i=&period to 2 by -1;
lastn{i}=lastn{i-1};
end;
lastn{1}=lag(balance);
if first.cid then do i=1 to .
lastn{i}=.;
end;
mean6=mean(of lastn1-lastn6);
mean12=mean(of lastn1-lastn12);
mean24=mean(of lastn1-lastn24);
run;[/code:0d6e2]作者: shiyiming 时间: 2007-10-1 09:30 标题: Re: 练手系列2:移动平均计算 简练~ 学习一下,还有其他练习题目吗?作者: shiyiming 时间: 2007-10-9 19:00 标题: Re: 练手系列2:移动平均计算 如何获取下一行的数据呢，与lag功能相反？作者: shiyiming 时间: 2007-10-18 10:30 标题: Re: 练手系列2:移动平均计算 这个问题简单了点，呵呵，用行号降序排列再用lag就是了作者: shiyiming 时间: 2007-10-26 22:57 标题: Re: 练手系列2:移动平均计算 方法一：（此方法效率方面可能不是最优）
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 时间: 2007-11-9 17:25 标题: Re: 练手系列2:移动平均计算 傻办法，创建12张表作left join。就是冗余太厉害。作者: shiyiming 时间: 2008-7-15 17:16 标题: Re: 练手系列2:移动平均计算 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 时间: 2008-7-15 17:25 标题: Re: 练手系列2:移动平均计算 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 时间: 2008-7-24 23:01 标题: Re: 练手系列2:移动平均计算 我的答案 <!-- 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>0 then miss=1;else miss=0;
if miss1>0 then x1=total1/miss1 ;
if miss2>0 then x2=total2/miss2;
totalmiss=totalmiss+miss;
lag6=lag6(totalmiss);
lag12=lag12(totalmiss);
lag66=lag6(balance);
lag1212=lag12(balance);
if balance>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;
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 时间: 2008-9-27 15:43 标题: Re: 练手系列2:移动平均计算 [code:thmsu4t3]proc sort data=a;
by cid month;
run;
data a;
set a;
by cid month;
if first.cid then num=0;
num+1;
run;
data avg1;
input avg1 8.3;
cards;
run;
data avg2;
input avg2 8.3;
cards;
run;
data aa;
set a;
call execute("data temp;
set a; where cid="||cid||" and num<"||num||" and num>="||num||"-6;
run;
proc means data=temp noprint;
var balance; output out=tmp mean=avg1; run;
data avg1; set avg1 tmp; run;");
run;
data aa;
set a;
call execute("data temp;
set a; where cid="||cid||" and num<"||num||" and num>="||num||"-12;
run;
proc means data=temp noprint;
var balance; output out=tmp mean=avg2; run;
data avg2; set avg2 tmp; run;");
run;
data avg;
set a;
by cid month;
if first.cid then delete;
run;
data final;
merge a avg;
by cid month;
drop num;
run;[/code:thmsu4t3]作者: shiyiming 时间: 2010-12-19 16:21 标题: Re: 练手系列2:移动平均计算 很有趣，这个是我常常问的面试题，不过不止计算移动平均这么简单。
proc sort data=a;
by cid month;
run;
data b;
set a;
by cid;
retain total;
if first.cid=1 then do;
total=0;
end;
if balance ne . then total=total+balance;
run;
data c;
set b;
by cid;
if first.cid=1 then do;
mv6=0;
mv12=0;
retain i k 0;
i=1;
end;
if i<12 and balance=. then k=k+1;
if first.cid ne 1 then do;
if i<6 then do;
mv6=lag(total)/(i-k);
end;
else do;
mv6=mean(lag1(balance),lag2(balance),lag3(balance),lag4(balance),lag5(balance),lag6(balance));
end;
if i<12 then do;
mv12=lag(total)/(i-k);
end;
else do;
mv12=mean(lag1(balance),lag2(balance),lag3(balance),lag4(balance),lag5(balance),
lag6(balance),lag7(balance),lag8(balance),lag9(balance),lag10(balance),lag11(balance),lag12(balance));
end;
i=i+1;
end;
if last.cid=1 then k=0;
run;