01312017 03:36 PM
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 
01312017 03:57 PM
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/SASCommunitiesLibrary/Howtocreateadatastepversionofyourdat...
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;
01312017 03:57 PM
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/SASCommunitiesLibrary/Howtocreateadatastepversionofyourdat...
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;
01312017 04:29 PM
This seems work. Thanks a lot!
DATA HAVE;
INPUT DATE$ 110 DENOMINATOR 1114 NUMERATOR 1517;
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;
01312017 04:07 PM
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
01312017 06:58 PM
I think you intend the array values to be automatically retained, yes? Are you missing a "_TEMPORARY_" attribute?
01312017 07:39 PM
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
Need further help from the community? Please ask a new question.