topic Re: Rolling 12 months in SAS Programming

Rolling 12 months

Ramakanthkrovi — Sun, 08 Sep 2019 23:00:19 GMT

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

Re: Rolling 12 months

novinosrin — Sun, 08 Sep 2019 23:18:37 GMT

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

Re: Rolling 12 months

Ramakanthkrovi — Sun, 08 Sep 2019 23:42:37 GMT

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

Re: Rolling 12 months

novinosrin — Mon, 09 Sep 2019 00:49:12 GMT

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;

Re: Rolling 12 months

novinosrin — Sun, 08 Sep 2019 23:53:09 GMT

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;

Re: Rolling 12 months

PaigeMiller — Sun, 08 Sep 2019 23:58:35 GMT

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.

Re: Rolling 12 months

Ramakanthkrovi — Mon, 09 Sep 2019 00:01:58 GMT

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?

Re: Rolling 12 months

Ramakanthkrovi — Mon, 09 Sep 2019 00:03:05 GMT

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?

Re: Rolling 12 months

novinosrin — Mon, 09 Sep 2019 00:10:57 GMT

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

Re: Rolling 12 months

Reeza — Mon, 09 Sep 2019 00:30:31 GMT

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-Analytics/ta-p/251260

Re: Rolling 12 months

novinosrin — Mon, 09 Sep 2019 00:40:35 GMT

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;

Re: Rolling 12 months

novinosrin — Mon, 09 Sep 2019 01:03:45 GMT


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;

Re: Rolling 12 months

Ramakanthkrovi — Mon, 09 Sep 2019 02:43:00 GMT

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})

Re: Rolling 12 months

Ramakanthkrovi — Mon, 09 Sep 2019 02:51:58 GMT

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

Re: Rolling 12 months

novinosrin — Mon, 09 Sep 2019 13:39:46 GMT

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 🙂

Re: Rolling 12 months

Ramakanthkrovi — Mon, 09 Sep 2019 23:21:45 GMT

Hi @novinosrin - sorry about that.

In the data that I have, there is no ID variable and so I replied that the lag worked which it did. I am just curious to know how having an ID variable will impact the code as I assumed it to be same as using a by statement.

Apologies about that. I am trying to learn this thing to an extent where I don't have to come back to the forum if I do get a request sooner or later to include an ID variable.

Re: Rolling 12 months

mkeintz — Tue, 10 Sep 2019 01:03:46 GMT

If you have an ID variable, then you need a way to prevent the initial sums of one ID from being contaminated by data from the prior ID - i.e., whenever you start a new ID, the sum has to be reset to zero or missing: And in addition, while the rolling sum requires subtracting the lag12(A) value, it should do so only when the lag12(region) is the same as the current region. I.e.:

data want;
  set have;
  by region;
  if first.region then sum_a=.;
  sum_a + a + -1*ifn(lag12(region)=region,lag12(a),0);
run;

This uses the following features of SAS

The BY statement tells sas to expect the data to be sorted, and to establish 2 dummy variables (first.region and last.region) that inform you whether the observations in hand is the start or end of a distinct region.
Statements like
variable + expression + expression + ...
(note the lack of an equals sign) are SUM statement which (a) add the values of the variable (and the expressions - which themselves can be variables) and put the result in the variable. It also tells SAS to retain the variable, enabling your rolling sum.
The IFN function tests the first argument. If true (i.e. the current obs is the 13th or later within a region), then IFN returns the 2nd argument: lag12(a). Otherwise it returns the 3rd argument (0).

But you ask about doing this for a set of variables (say A, B, and C). The natural SAS approach is to declare A B and C as an array, and to define an analogous array with variables SUMA, SUMB, and SUMC. You might be tempted to do this, which just puts the sum statement (and it associated IFN and LAG functions) inside a loop over the 3 variables A, B, C.

data want;
  set have;
  by region;
  array vars {3} a b c;
  array sums {3} suma sumb  sumc;
  if first.region then call missing(of sums{*});

  do v=1 to 3;
    sums{v} + vars{v} + -1*ifn(lag12(region)=region,lag12(vars{v}),0);
  end;
run;

But this would give the wrong result, because the "LAG12(region)" function would be run 3 times for each operation. The LAG function is a FIFO queue updater, so in this case it would step through the underlying history of region values 3 times as fast as you want. The best option is to take the LAG12(region) evaluation outside of the loop:

data want (drop=_LGREG12 v);
  set have;
  by region;
  array vars {3} a b c;
  array sums {3} suma sumb  sumc;
  if first.region then call missing(of sums{*});

  _LGREG12=lag12(region);
  do v=1 to 3;
    sums{v} + vars{v} + -1*ifn(_LGREG12=region,lag12(vars{v}),0);
  end;
run;

regards,

Mark

Re: Rolling 12 months

rekhaa — Thu, 23 Jan 2020 08:18:01 GMT

Can you share the same SQL code with the use of ID variable