sasproc-sqldo-loopsdatastep

SAS code - sum of last N rows for every row


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.


Solution

  • 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;