topic Re: Carrying forward sum values upto 15 months for each row in SAS Programming

Carrying forward sum values upto 15 months for each row

deepika951 — Wed, 24 Mar 2021 16:34:56 GMT

Dear All,
I have a SAS dataset with numerator & denominator for each month-year & I would like to carry forward the sum of these numerators & denominators for upto 15 months from each row month-year value.
I have:

Month_Yr	Num	Den
Dec-2018	10	15
Jan-2019	40	56
Feb-2019	35	40
Mar-2019	26	37
Apr-2019	34	37
May-2019	67	68
Jun-2019	24	24
Jul-2019	54	58
Aug-2019	56	57
Sep-2019	29	35
Oct-2019	40	45
Nov-2019	26	26
Dec-2019	45	47
Jan-2020	36	37
Feb-2020	35	40
Mar-2020	20	30
Apr-2020	16	26
May-2020	46	50

I want: for each date, look back 15 months & include those values in the sum (Num_sum for numerator sums, for example). Here the 1st 15 month cut off ends at Mar 2020, so starting Apr 2020, I want the sum to include all "num" values except the Dec-2018 (as it is >15 months back in time).

Month_Yr	Num	Den	Sum_Num
Dec-2018	10	15	10
Jan-2019	40	56	50
Feb-2019	35	40	85
Mar-2019	26	37	111
Apr-2019	34	37	& so on until 15th month
May-2019	67	68
Jun-2019	24	24
Jul-2019	54	58
Aug-2019	56	57
Sep-2019	29	35
Oct-2019	40	45
Nov-2019	26	26
Dec-2019	45	47
Jan-2020	36	37
Feb-2020	35	40
Mar-2020	20	30	577
Apr-2020	16	26	567
May-2020	46	50

Is there any guidance to write a code for this?

Thank you so much.

Re: Carrying forward sum values upto 15 months for each row

Reeza — Wed, 24 Mar 2021 17:11:07 GMT

Do you have a SAS/ETS license? You can check that with the following: PROC PRODUCT_STATUS; RUN;

If you see SAS/ETS listed, you have it and can use PROC EXPAND and the CONVERT statement to get a 15 month running total. To confirm, if <15 months of data are available it just uses the data up to that point? Can you please extend your example to illustrate what happens after the 15th month so it's clear?

Re: Carrying forward sum values upto 15 months for each row

Astounding — Wed, 24 Mar 2021 19:45:01 GMT

If your data is complete ... meaning that all months are accounted for with nothing missing in the middle of the series ... the code is relatively easy:

data want;
   set have;
   back16 = lag16(num);
   num_sum + num;
   num_sum = sum(num_sum, -back16);
run;