SAS中文论坛曾有个关于移动平均的练手题目,要求计算每个客户在每个月的前6个月和前12个月的移动平均余额。内容见链接:http://www.mysas.net/forum/viewtopic.php?f=4&t=3458&hilit=%E7%A7%BB%E5%8A%A8
模拟数据为:
data a;
infile datalines;
input cid month yymmn6. balance;
format month yymm.;
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
;
run;
在这把该精贴答案整理一下
xsmile解答:
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的代码
%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;
byes的代码
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;
waterlwh的解答
proc delete data=_all_;run;
proc sort data=a;by cid;run;
%macro tt(g);
data b&g;
set a(where=(cid=&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.cid then call execute('%tt('||cid||')');
run;
proc sql;
select count(distinct cid) into :cnt from a;
quit;
%macro test;
%do j=1 %to &cnt;
proc append base=b0 data=b&j force;
run;
%end;
%mend;
%test
proc print data=b0;run;
wangshilu的一种思路:
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;
oloolo的代码:
data av/view=av;
set a; by cid;
b_lag=lag(balance);
if first.cid then b_lag=.;
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.5 @60 mean12= 7.5;;
run;