我的问题是计算每个id和每个日期的累积总和,同时考虑到之前15天的滑动期。如果累计总和超过10k,则变量top递增。
治疗仅针对觉恩。
以下是所需结果的示例:
id app_date price cum top
1 29-juin-20 4000 4 000 .
1 13-juin-20 5000 45 000 1
1 13-juin-20 6000 40 000 2
1 11-juin-20 7000 34 000 3
1 10-juin-20 8000 27 000 4
1 01-juin-20 9000 19 000 5
1 30-mai-20 10000 10 000 .
proc sort data = tab out= tab1;
by id descending app_date;
run;
data tab2;
set tab1;
%let annee=2020;
%let month=06;
by id;
retain last_date date_last_d CUM;
if first.id then do;
last_date =app_date;
date_last_dem = app_date;
CUM=0;
end;
if month(date_last_d) =&month. then do ;
diff= date_last_d -app_date;
CUM= price+ CUM;
end;
if diff>15 then do;
diff = .;
CUM =.;
last_date =app_date;
date_last_d = app_date;
end;
if last.id and CUM>10000 then top= top+1 ;
output;
last_date=app_date;
format last_date DDMMYY10.;
format date_last_d DDMMYY10.;
format CUM 14.2;
run;
我可以在第一次迭代中这样做,但不能对所有的行都这样做。
这个怎么样?
data have;
input Cnt Price ID App_date :ddmmyy10.;
format App_date ddmmyy10.;
datalines;
1 2265 534 30/05/2020
2 2330 4594 27/06/2020
3 1360 723 14/05/2020
4 1393 723 14/05/2020
5 2400 101666 12/06/2020
6 2411 101666 12/06/2020
7 2400 101666 11/06/2020
8 2400 101666 11/06/2020
9 2527 101666 10/06/2020
10 2536 101666 10/06/2020
11 2458 101666 04/06/2020
12 2758 1088 30/05/2020
13 4412 1056 13/06/2020
14 1870 1255 30/06/2020
15 4198 1255 14/05/2020
;
data want(drop = c k p dt);
dcl hash h(ordered : "Y");
h.definekey("c");
h.definedata("c", "p", "dt");
h.definedone();
dcl hiter i("h");
do c = 1 by 1 until (last.ID);
set have(rename=(App_Date=dt Price=p));
by ID notsorted;
h.add();
end;
do k = 1 by 1 until (last.ID);
set have;
by ID notsorted;
cum = 0;
do while (i.next() = 0);
if App_Date - 15 <= dt <= App_Date & k <= c then cum + p;
end;
if cum > 10000 then top + 1;
else top = .;
output;
end;
h.clear();
run;