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 |
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;
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;
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;
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
I think you intend the array values to be automatically retained, yes? Are you missing a "_TEMPORARY_" attribute?
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.