DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor LL5
Contributor
Posts: 44
Accepted Solution

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: 11,343

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;

View solution in original post


All Replies
Solution
‎02-02-2017 08:36 PM
Super User
Posts: 11,343

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 LL5
Contributor
Posts: 44

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,492

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

 

Trusted Advisor
Posts: 1,022

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

@art297:

 

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

PROC Star
Posts: 7,492

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

@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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 202 views
  • 3 likes
  • 4 in conversation