Solved
Contributor
Posts: 55

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

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

Accepted Solutions
Solution
‎02-02-2017 08:36 PM
Super User
Posts: 12,316

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

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;```

All Replies
Solution
‎02-02-2017 08:36 PM
Super User
Posts: 12,316

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

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;```
Contributor
Posts: 55

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

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;

PROC Star
Posts: 7,845

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

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

Posts: 1,214

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

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

PROC Star
Posts: 7,845

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

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

☑ This topic is solved.