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

[LL5]

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

[ballardw · Accepted solution]

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-dat...

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;

[LL5]

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;

[art297]

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

[mkeintz]

@art297:

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

[art297]

@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