SAS code - sum of last N rows for every row

Question

I have a dataset like this for each ID;

Months	ID	Number
2018-07-01	1	0
2018-08-01	1	0
2018-09-01	1	1
2018-10-01	1	3
2018-11-01	1	1
2018-12-01	1	2
2019-01-01	1	0
2019-02-01	1	0
2019-03-01	1	1
2019-04-01	1	0
2019-05-01	1	0
2019-06-01	1	0
2019-07-01	1	1
2019-08-01	1	0
2019-09-01	1	0
2019-10-01	1	2
2019-11-01	1	0
2019-12-01	1	0
2020-01-01	1	0
2020-02-01	1	0
2020-03-01	1	0
2020-04-01	1	0
2020-05-01	1	0
2020-06-01	1	0
2020-07-01	1	0
2020-08-01	1	1
2020-09-01	1	0
2020-10-01	1	0
2020-11-01	1	1
2020-12-01	1	0
2021-01-01	1	0
2021-02-01	1	1
2021-03-01	1	1
2021-04-01	1	0
2018-07-01	2	0
.......	.......	.......

(Similar values for each ID)

I want a dataset like this;

Months	ID	Number	Sum_Next_6Number
2018-07-01	1	0	7
2018-08-01	1	0	7
2018-09-01	1	1	7
2018-10-01	1	3	4
2018-11-01	1	1	3
2018-12-01	1	2	1
2019-01-01	1	0	2
2019-02-01	1	0	2
2019-03-01	1	1	1
2019-04-01	1	0	3
2019-05-01	1	0	3
2019-06-01	1	0	3
2019-07-01	1	1	2
2019-08-01	1	0	2
2019-09-01	1	0	2
2019-10-01	1	2	0
2019-11-01	1	0	0
2019-12-01	1	0	0
2020-01-01	1	0	0
2020-02-01	1	0	1
2020-03-01	1	0	1
2020-04-01	1	0	1
2020-05-01	1	0	2
2020-06-01	1	0	2
2020-07-01	1	0	2
2020-08-01	1	1	2
2020-09-01	1	0	3
2020-10-01	1	0	3
2020-11-01	1	1	Nan
2020-12-01	1	0	Nan
2021-01-01	1	0	Nan
2021-02-01	1	1	Nan
2021-03-01	1	1	Nan
2021-04-01	1	0	Nan
2018-07-01	2	0	0
.......	.......	.......	.......

If there is no 6 months left then this values should be Nan.

Is there a way to do this? Thank you in advance.

Hi! What code have you tried? What is "Nan" - SAS doesn't have "Nan", do you mean missing value? Or is that a text column? — Joe, Aug 20 '21 at 17:51
Hi! Yes I meant missing value. I was trying to solve with proc sql but I should have tried do loop. It's solved thank you! — Deniz, Aug 22 '21 at 13:57

score 0 · Accepted Answer · answered Aug 20 '21 at 16:28

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;

SAS code - sum of last N rows for every row

1 Answers1