Hi, I recently asked a question about SAS sum of next N rows for every row here .
This is exactly the answer to the question and works well (by one column).
data want(drop = i n);
set have curobs = c nobs = nobs;
Sum_Next_6Numbers = 0;
do p = c + 1 to 6 + c;
if p > nobs then do;
Sum_Next_6Numbers = .; leave;
end;
set have(keep = Number ID rename = (Number = n id = i)) point = p;
if id ne i then do;
Sum_Next_6Numbers = .; leave;
end;
Sum_Next_6Numbers + n;
end;
run;
So basically, I want to sum up next 6 row's values(number column) by 2 columns(id and id2). If there is no 6 months left then this values should be Nan.
I have a dataset like this for each ID and ID2;
data have;
input Months ID ID2 Number;
2018-07-01 1 1 0
2018-08-01 1 1 0
2018-09-01 1 1 0
2018-10-01 1 1 1
2018-11-01 1 1 0
2018-12-01 1 1 1
2019-01-01 1 1 0
2019-02-01 1 1 0
2019-03-01 1 1 0
2019-04-01 1 1 0
2019-05-01 1 1 0
2019-06-01 1 1 0
2019-07-01 1 1 0
2019-08-01 1 1 0
2019-09-01 1 1 0
2019-10-01 1 1 0
2019-11-01 1 1 0
2019-12-01 1 1 0
2020-01-01 1 1 0
2020-02-01 1 1 0
2020-03-01 1 1 0
2020-04-01 1 1 0
2020-05-01 1 1 0
2020-06-01 1 1 0
2020-07-01 1 1 0
2020-08-01 1 1 0
2020-09-01 1 1 0
2020-10-01 1 1 0
2020-11-01 1 1 1
2020-12-01 1 1 0
2021-01-01 1 1 0
2021-02-01 1 1 0
2021-03-01 1 1 0
2021-04-01 1 1 0
2018-07-01 1 2 0
2018-08-01 1 2 0
2018-09-01 1 2 0
2018-10-01 1 2 0
2018-11-01 1 2 0
2018-12-01 1 2 0
2019-01-01 1 2 0
2019-02-01 1 2 0
2019-03-01 1 2 0
2019-04-01 1 2 0
2019-05-01 1 2 0
2019-06-01 1 2 0
2019-07-01 1 2 0
2019-08-01 1 2 0
2019-09-01 1 2 0
2019-10-01 1 2 0
2019-11-01 1 2 0
2019-12-01 1 2 0
2020-01-01 1 2 0
2020-02-01 1 2 0
2020-03-01 1 2 0
2020-04-01 1 2 0
2020-05-01 1 2 0
2020-06-01 1 2 0
2020-07-01 1 2 0
2020-08-01 1 2 0
2020-09-01 1 2 0
2020-10-01 1 2 0
2020-11-01 1 2 0
2020-12-01 1 2 0
2021-01-01 1 2 0
2021-02-01 1 2 0
2021-03-01 1 2 0
2021-04-01 1 2 0
2018-07-01 2 1 0
2018-08-01 2 1 0
2018-09-01 2 1 0
2018-10-01 2 1 0
2018-11-01 2 1 0
2018-12-01 2 1 0
2019-01-01 2 1 0
2019-02-01 2 1 0
2019-03-01 2 1 0
2019-04-01 2 1 0
2019-05-01 2 1 0
2019-06-01 2 1 0
2019-07-01 2 1 0
2019-08-01 2 1 0
2019-09-01 2 1 0
2019-10-01 2 1 0
2019-11-01 2 1 0
2019-12-01 2 1 0
2020-01-01 2 1 0
2020-02-01 2 1 0
2020-03-01 2 1 0
2020-04-01 2 1 0
2020-05-01 2 1 0
2020-06-01 2 1 0
2020-07-01 2 1 0
2020-08-01 2 1 1
2020-09-01 2 1 0
2020-10-01 2 1 0
2020-11-01 2 1 0
2020-12-01 2 1 0
2021-01-01 2 1 0
2021-02-01 2 1 0
2021-03-01 2 1 0
2021-04-01 2 1 0
....... ....... .......
I want a dataset like this;
data want;
input Months ID ID2 Number Sum_Next_6Numbers;
2018-07-01 1 1 0 2
2018-08-01 1 1 0 2
2018-09-01 1 1 0 2
2018-10-01 1 1 1 1
2018-11-01 1 1 0 1
2018-12-01 1 1 1 0
2019-01-01 1 1 0 0
2019-02-01 1 1 0 0
2019-03-01 1 1 0 0
2019-04-01 1 1 0 0
2019-05-01 1 1 0 0
2019-06-01 1 1 0 0
2019-07-01 1 1 0 0
2019-08-01 1 1 0 0
2019-09-01 1 1 0 0
2019-10-01 1 1 0 0
2019-11-01 1 1 0 0
2019-12-01 1 1 0 0
2020-01-01 1 1 0 0
2020-02-01 1 1 0 0
2020-03-01 1 1 0 0
2020-04-01 1 1 0 0
2020-05-01 1 1 0 1
2020-06-01 1 1 0 1
2020-07-01 1 1 0 1
2020-08-01 1 1 0 1
2020-09-01 1 1 0 1
2020-10-01 1 1 0 1
2020-11-01 1 1 1 NaN
2020-12-01 1 1 0 NaN
2021-01-01 1 1 0 NaN
2021-02-01 1 1 0 NaN
2021-03-01 1 1 0 NaN
2021-04-01 1 1 0 NaN
2018-07-01 1 2 0 0
2018-08-01 1 2 0 0
2018-09-01 1 2 0 0
2018-10-01 1 2 0 0
2018-11-01 1 2 0 0
2018-12-01 1 2 0 0
2019-01-01 1 2 0 0
2019-02-01 1 2 0 0
2019-03-01 1 2 0 0
2019-04-01 1 2 0 0
2019-05-01 1 2 0 0
2019-06-01 1 2 0 0
2019-07-01 1 2 0 0
2019-08-01 1 2 0 0
2019-09-01 1 2 0 0
2019-10-01 1 2 0 0
2019-11-01 1 2 0 0
2019-12-01 1 2 0 0
2020-01-01 1 2 0 0
2020-02-01 1 2 0 0
2020-03-01 1 2 0 0
2020-04-01 1 2 0 0
2020-05-01 1 2 0 0
2020-06-01 1 2 0 0
2020-07-01 1 2 0 0
2020-08-01 1 2 0 0
2020-09-01 1 2 0 0
2020-10-01 1 2 0 0
2020-11-01 1 2 0 NaN
2020-12-01 1 2 0 NaN
2021-01-01 1 2 0 NaN
2021-02-01 1 2 0 NaN
2021-03-01 1 2 0 NaN
2021-04-01 1 2 0 NaN
2018-07-01 2 1 0 0
2018-08-01 2 1 0 0
2018-09-01 2 1 0 0
2018-10-01 2 1 0 0
2018-11-01 2 1 0 0
2018-12-01 2 1 0 0
2019-01-01 2 1 0 0
2019-02-01 2 1 0 0
2019-03-01 2 1 0 0
2019-04-01 2 1 0 0
2019-05-01 2 1 0 0
2019-06-01 2 1 0 0
2019-07-01 2 1 0 0
2019-08-01 2 1 0 0
2019-09-01 2 1 0 0
2019-10-01 2 1 0 0
2019-11-01 2 1 0 0
2019-12-01 2 1 0 0
2020-01-01 2 1 0 0
2020-02-01 2 1 0 1
2020-03-01 2 1 0 1
2020-04-01 2 1 0 1
2020-05-01 2 1 0 1
2020-06-01 2 1 0 1
2020-07-01 2 1 0 1
2020-08-01 2 1 1 0
2020-09-01 2 1 0 0
2020-10-01 2 1 0 0
2020-11-01 2 1 0 NaN
2020-12-01 2 1 0 NaN
2021-01-01 2 1 0 NaN
2021-02-01 2 1 0 NaN
2021-03-01 2 1 0 NaN
2021-04-01 2 1 0 NaN
....... ....... .......
I did it with a nested for loop in python. I'm new to sas and I'm confused. Is there a way to do this in SAS? Thanks in advance.
Do you have SAS/ETS? This is incredibly trivial with PROC EXPAND - sort of like using the right package in Python.
title;
data test;
input year qtr x;
date = yyq( year, qtr );
format date yyqc.;
datalines;
1989 3 5238
1989 4 5289
1990 1 5375
1990 2 5443
1990 3 5514
1990 4 5527
1991 1 5557
1991 2 5615
;
proc expand data=test out=out method=none;
id date;
convert x = x_lag2 / transformout=(lag 2);
convert x = x_lag1 / transformout=(lag 1);
convert x;
convert x = x_lead1 / transformout=(lead 1);
convert x = x_lead2 / transformout=(lead 2);
convert x = x_movave / transformout=(movave 3);
run;
title "Transformed Series";
proc print data=out;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.