SAS sum of next N rows for every row by 2 columns

zdc — Tue, 31 Aug 2021 14:08:48 GMT

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.

Re: SAS sum of next N rows for every row by 2 columns

Reeza — Tue, 31 Aug 2021 16:17:48 GMT

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;

topic SAS sum of next N rows for every row by 2 columns in SAS Programming

SAS sum of next N rows for every row by 2 columns

Re: SAS sum of next N rows for every row by 2 columns