cianfen的部落格

對每一筆都產生3月前到今天的出現資料的和

EX: 20010701, 希望加總 20010402~20010701
data d;
input id date no ;
cards;
1 20000229 1
1 20000528 1
1 20000529 1
1 20000630 1
1 20000701 1
1 20000702 2
1 20000704 1
1 20000708 2
1 20000801 1
1 20000802 2
1 20000901 1
1 20001001 2
1 20001101 4
1 20001101 4
;
run;

* 每一筆先造出 begin date-> d_last3 ;
data d1;
set d;
d_date= MDY( mod(int(date/100),100) , mod(date,100) , int(date/10000) ); * 轉換為sas 日期; 取出月日年.
monthA=month(d_date);
dayA=day(d_date);
yearA=year(d_date);
dlast = intnx('month',d_date,1)-1;
d_last3=intnx('month',d_date,-3)+dayA; *算出3月前 的一天;
run;

 * 在日期範圍內的做加總 ;
proc sql;
create table result
as select distinct a.id, a.date, sum(b.no) as ACC_NO
from d1 as a, d1 as b
where a.id=b.id and b.d_date>=a.d_last3 and b.d_date<=a.d_date
group by a.id, a.date;
quit;

*使用 [Cartesian product]; 再濾出需要的項目.