BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LL5
Pyrite | Level 9 LL5
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

5 REPLIES 5
ballardw
Super User

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
Pyrite | Level 9 LL5
Pyrite | Level 9

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
Opal | Level 21

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
PROC Star

@art297:

 

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
art297
Opal | Level 21

@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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 7143 views
  • 4 likes
  • 4 in conversation