topic Re: How to calculate 3 month rolling average and weight average % in SAS in SAS Programming

How to calculate 3 month rolling average and weight average % in SAS

LL5 — Tue, 31 Jan 2017 20:36:35 GMT

I have a dataset like below and I am trying to calculate the rolling 3 month average for both of the denominator and numerator, and lastly try to get the weight average % (Rolling 3M average of numerator / rolling 3M average of denominator). Is there any way to do it? Any suggestions would be gladly appreciated.

DATE	DENOMINATOR	NUMERATOR
11/30/2015	927	62
12/31/2015	968	27
1/29/2016	619	36
2/29/2016	433	24
3/31/2016	642	60
4/29/2016	286	79
5/31/2016	962	81
6/30/2016	194	55
7/29/2016	892	54
8/31/2016	464	74
9/30/2016	600	4
10/31/2016	397	42
11/30/2016	896	35
12/30/2016	742	58

The end result would be like this:

DATE	DENOMINATOR	NUMERATOR	Rolling 3M Average of Denominator	Rolling 3M average of Numerator	Rolling 3M average of Numerator/ Rolling 3M Average of Denominator
11/30/2015	927	62
12/31/2015	968	27
1/29/2016	619	36	838	42	0.050
2/29/2016	433	24	673	29	0.043
3/31/2016	642	60	565	40	0.071
4/29/2016	286	79	454	54	0.120
5/31/2016	962	81	630	73	0.116
6/30/2016	194	55	481	72	0.149
7/29/2016	892	54	683	63	0.093
8/31/2016	464	74	517	61	0.118
9/30/2016	600	4	652	44	0.067
10/31/2016	397	42	487	40	0.082
11/30/2016	896	35	631	27	0.043
12/30/2016	742	58	678	45	0.066

Re: How to calculate 3 month rolling average and weight average % in SAS

ballardw — Tue, 31 Jan 2017 20:57:24 GMT

Followiing is untested code because what every you pasted for data is not amemable to writing code against. Better is to paste data in the form of a data step. If you have a SAS data set instructions here will help you create data step code from the data: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

You may want to subset your data to only variables of interest and enough records to exercise the rules.

This should work though

data want;
   set have;
   r3mMdenom = mean(denominator,lag(denominator),lag2(denominator);
   r3mMnum   = mean(numerator,lag(numerator),lag2(numerator);
   /* since you don't want the partials*/
   if _n_<3 then call missing(r3mMdenom, r3mMnum);
   else r3mpercent= r3mMnum/r3mMdenom;
run;

Re: How to calculate 3 month rolling average and weight average % in SAS

art297 — Tue, 31 Jan 2017 21:07:58 GMT

Another way to approach the problem is to use a FIFO stack. e.g.:

data want (drop=_:);
  set have;
  array n[0:2] _n0-_n2;
  array d[0:2] _d0-_d2;
  retain _n0-_n2 _d0-_d2;
  _index=mod(_n_,3);
  n[_index]=NUMERATOR;
  d[_index]=DENOMINATOR;
  if _n_ ge 3 then do;
    Rolling_3M_Average_Denominator=sum(of d(*))/3;
    Rolling_3M_average_Numerator=sum(of n(*))/3;
    Numerator_div_Denominator=
     Rolling_3M_average_Numerator/Rolling_3M_Average_Denominator;
  end;
run;

HTH,

Art, CEO, AnalystFinder.com

Re: How to calculate 3 month rolling average and weight average % in SAS

LL5 — Tue, 31 Jan 2017 21:29:48 GMT

This seems work. Thanks a lot!

DATA HAVE;

INPUT DATE$ 1-10 DENOMINATOR 11-14 NUMERATOR 15-17;

DATALINES;

11/30/2015 927 62

12/31/2015 968 27

1/29/2016 619 36

2/29/2016 433 24

3/31/2016 642 60

4/29/2016 286 79

5/31/2016 962 81

6/30/2016 194 55

7/29/2016 892 54

8/31/2016 464 74

9/30/2016 600 4

10/31/2016 397 42

11/30/2016 896 35

12/30/2016 742 58

;

RUN;

data want;

set have;

r3mMdenom = mean(denominator,lag(denominator),lag2(denominator));

r3mMnum = mean(numerator,lag(numerator),lag2(numerator));

/* since you don't want the partials*/

if _n_<3 then call missing(r3mMdenom, r3mMnum);

else r3mpercent= r3mMnum/r3mMdenom;

run;

Re: How to calculate 3 month rolling average and weight average % in SAS

mkeintz — Tue, 31 Jan 2017 23:58:52 GMT

@art297:

I think you intend the array values to be automatically retained, yes? Are you missing a "_TEMPORARY_" attribute?

Re: How to calculate 3 month rolling average and weight average % in SAS

art297 — Wed, 01 Feb 2017 00:39:48 GMT

@mkeintz,

While I had retained the values, you're absolutely correct. It would have been even simpler to define them as being _temporary_. Similarly, using the mean function, would even further simplify the code. Here is a revised version:

data want (drop=_:);
  set have;
  array n[0:2] _temporary_;
  array d[0:2] _temporary_;
  _index=mod(_n_,3);
  n[_index]=NUMERATOR;
  d[_index]=DENOMINATOR;
  if _n_ ge 3 then do;
    Rolling_3M_Average_Denominator=mean(of d(*));
    Rolling_3M_average_Numerator=mean(of n(*));
    Numerator_div_Denominator=
     Rolling_3M_average_Numerator/Rolling_3M_Average_Denominator;
  end;
run;

Thanks for pointing that out,

Art