Rolling 12 months

[Ramakanthkrovi]

Hi Team,

I don't have sas/ets and was trying to see if this question has been raised and answered previously.

I am trying to create a rolling 12 month total which calculates the sum of the last 12 months.

Month	A	sum(rolling 12 months)
April 2018	1	.
May 2018	2	3
June 2018	3	6
July 2018	4	10
August 2018	5	15
September 2018	6	21
October 2018	7	28
November 2018	8	36
December 2018	9	45
January 2019	10	55
February 2019	11	66
March 2019	12	78
April 2019	13	90
May 2019	14	102
June 2019	15	114
July 2019	16	126
August 2019	17	138
September 2019	18	150
October 2019	19	162
November 2019	20	174

[novinosrin]

12 month windows super simple , however do you have an ID variable or is it just that one set? i.e no groups?

[Ramakanthkrovi]

No ID variable. for every month I just need the actual (a) and sum(a to a-11).

[novinosrin]

Hi @Ramakanthkrovi   Thank you. The logic is very simple.

The window condition 

on intnx('mon',a.month,-11)<=b.month<=a.month

according to your needs below

data have;
input Month :monyy7.	A ;
format month monyy7.;
cards;
Apr-18	1
May-18	2
Jun-18	3
Jul-18	4
Aug-18	5
Sep-18	6
Oct-18	7
Nov-18	8
Dec-18	9
Jan-19	10
Feb-19	11
Mar-19	12
Apr-19	13
May-19	14
Jun-19	15
Jul-19	16
Aug-19	17
Sep-19	18
Oct-19	19
Nov-19	20
;

proc sql;
create table want as
select a.* ,sum(b.a) as sum 
from have a left join have b
on intnx('mon',a.month,-11)<=b.month<=a.month
group by a.month ,a.a
order by a.month;
quit;

[novinosrin · Accepted solution]

Or Use lag

data have;
input Month :monyy7.	A ;
format month monyy7.;
cards;
Apr-18	1
May-18	2
Jun-18	3
Jul-18	4
Aug-18	5
Sep-18	6
Oct-18	7
Nov-18	8
Dec-18	9
Jan-19	10
Feb-19	11
Mar-19	12
Apr-19	13
May-19	14
Jun-19	15
Jul-19	16
Aug-19	17
Sep-19	18
Oct-19	19
Nov-19	20
;
data want;
set have;
s+a;
k=lag12(s);
want=s-sum(k,0);
drop k s;
run;

[PaigeMiller]

If you have PROC EXPAND in your SAS license, use that, it's a lot easier than having to write a program to do it yourself.

[Ramakanthkrovi]

Hi @PaigeMiller - 

no, unfortunately we don't have sas/ets licence.

I was also looking to do this in SAS VA (7.4) if possible. is it simpler than writing a program?

[Reeza]

VA has built in functionality to handle date dimensions - is your variable formatted and identified as a date in VA?

https://communities.sas.com/t5/SAS-Communities-Library/3-FAQs-on-date-calculations-in-SAS-Visual-Ana...

[Ramakanthkrovi]

I had to do this to get a rolling 12 month figure. the article is not informative for what I was trying to do. otherwise it works.

RelativePeriod(_Sum_, 'NDC'n, _ApplyAllFilters_, 'Reg_Mon'n, _ByMonth_, 0, _Full_, {Date}) + RelativePeriod(_Sum_, 'NDC'n, _ApplyAllFilters_, 'Reg_Mon'n, _ByMonth_, -1, _Full_, {Date}) + RelativePeriod(_Sum_, 'NDC'n, _ApplyAllFilters_, 'Reg_Mon'n, _ByMonth_, -2, _Full_, {Date}) + RelativePeriod(_Sum_, 'NDC'n, _ApplyAllFilters_, 'Reg_Mon'n, _ByMonth_, -3, _Full_, {Date})

+ ...

+RelativePeriod(_Sum_, 'NDC'n, _ApplyAllFilters_, 'Reg_Mon'n, _ByMonth_, -11, _Full_, {Date}) + RelativePeriod(_Sum_, 'NDC'n, _ApplyAllFilters_, 'Reg_Mon'n, _ByMonth_, -12, _Full_, {Date})

[Ramakanthkrovi]

Thank you @novinosrin - The lag worked great.

Is it simpler to do this way if we have multiple columns or is there a way I can do it in SAS VA 7.4?

[novinosrin]

I have no idea about SAS VA and have never used it. The LAG usage here is mere simple arithmetic. I suggest using that. The number of columns is immaterial as your monthyear variable is continuous. Programming using continuous variable is generally easy

[novinosrin]

Hello @Ramakanthkrovi  Some extra fun with temp array

data have;
input Month :monyy7.	A ;
format month monyy7.;
cards;
Apr-18	1
May-18	2
Jun-18	3
Jul-18	4
Aug-18	5
Sep-18	6
Oct-18	7
Nov-18	8
Dec-18	9
Jan-19	10
Feb-19	11
Mar-19	12
Apr-19	13
May-19	14
Jun-19	15
Jul-19	16
Aug-19	17
Sep-19	18
Oct-19	19
Nov-19	20
;

data want;
 do _n_=1 by 1 until(z); 
  set have end=z;
  array t(0:11) _temporary_;
  t(mod(_n_,12))=a;
  s=sum(of t(*));
  output;
 end;
run;

[novinosrin]

data have;
input Month :monyy7.	A ;
format month monyy7.;
cards;
Apr-18	1
May-18	2
Jun-18	3
Jul-18	4
Aug-18	5
Sep-18	6
Oct-18	7
Nov-18	8
Dec-18	9
Jan-19	10
Feb-19	11
Mar-19	12
Apr-19	13
May-19	14
Jun-19	15
Jul-19	16
Aug-19	17
Sep-19	18
Oct-19	19
Nov-19	20
;
data want;
if _n_=1 then do;
  if 0 then set have(rename=(month=_m a=_a));
   dcl hash H (dataset:'have(rename=(month=_m a=_a))') ;
   h.definekey  ("_m") ;
   h.definedata ("_m","_a") ;
   h.definedone () ;
   dcl hiter hi('h');
 end;
 set have;
 do while(hi.next()=0);
  if intnx('mon',month,-11)<=_m<=month then sum=sum(sum,_a);
 end;
 drop _:;
 run;

[Ramakanthkrovi]

Thank you @novinosrin - if there is an ID variable, what will be the impact on the code?

Region	Month	A	sum(rolling 12 months)
Australia	April 2018	1	.
Australia	May 2018	2	3
Australia	June 2018	3	6
Australia	July 2018	4	10
Australia	August 2018	5	15
Australia	September 2018	6	21
Australia	October 2018	7	28
Australia	November 2018	8	36
Australia	December 2018	9	45
Australia	January 2019	10	55
Australia	February 2019	11	66
Australia	March 2019	12	78
Australia	April 2019	13	90
Australia	May 2019	14	102
NZ	April 2018	1	.
NZ	May 2018	2	3
NZ	June 2018	3	6
NZ	July 2018	4	10
NZ	August 2018	5	15
NZ	September 2018	6	21
NZ	October 2018	7	28
NZ	November 2018	8	36
NZ	December 2018	9	45
NZ	January 2019	10	55
NZ	February 2019	11	66
NZ	March 2019	12	78
NZ	April 2019	13	90
NZ	May 2019	14	102

[novinosrin]

Hello @Ramakanthkrovi  Now you are talking. I asked earlier the same and you categorically said NO. 🙂

Anyways, let us know which solution in (SQL,LAG,ARRAY,HASH) you want to use and we shall work with that alone. Plus, I got the initial impression that you didn't really seem to go for a programming solution and rather a VA solution. So please think over and ask your teammates(senior)/boss at work as to what should be done and how it should be done. 

Finally, while the slight modification is rather too simple however would you/your team be able to maintain,modify and update programming solutions considering you may be completely from a different background as a whole. Believe it or not, at my place of work any of the advance techniques are NOT welcome coz many even prefer 1000 lines of hard coding and some seniors deem that as "different" coding style while I call it "substandard". lol 🙂