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

Hello,

 

I am interested in calculating 12 month forward moving Average.

 

I am looking for something like below.

 

Example data:                                                                                                       

Input Sample

observation_month      Total                                                    

2007-01                          30                                                   

2007-02                          10                                                   

2007-03                           20                                                                             

2007-04                          60                                                                        

2007-05                          70

2007-06                          60

2007-07                          30

2007-08                          20

2007-09                          60

2007-10                          80

2007-11                          90

2007-12                          40

2008-01                         60

 

 

Out Put

 

Observation_date            12_month_forward average

 

2007-01                                        47.5               (Average from 2007-01 to 2007-12)

2007-02                                         50                 (Average from 2007-02 to 2018-01)

.

.

.

.

1 ACCEPTED SOLUTION

Accepted Solutions
cau83
Pyrite | Level 9

Do you have SAS/ETS? If so, PROC EXPAND will allow you to do this, it allows for many types of moving averages. The reverse transformation option will need to be combined with the desired moving average transformation option.

 

From http://morgan.dartmouth.edu/Docs/sas92/support.sas.com/documentation/cdl/en/etsug/60372/HTML/default...

 

"You can compute a forward moving time window operation by combining a backward moving time window operator with the REVERSE operator. For example, the following statement computes a five-period forward moving average of X."

   convert x=y / transformout=( reverse movave 5 reverse );

 

View solution in original post

4 REPLIES 4
cau83
Pyrite | Level 9

Do you have SAS/ETS? If so, PROC EXPAND will allow you to do this, it allows for many types of moving averages. The reverse transformation option will need to be combined with the desired moving average transformation option.

 

From http://morgan.dartmouth.edu/Docs/sas92/support.sas.com/documentation/cdl/en/etsug/60372/HTML/default...

 

"You can compute a forward moving time window operation by combining a backward moving time window operator with the REVERSE operator. For example, the following statement computes a five-period forward moving average of X."

   convert x=y / transformout=( reverse movave 5 reverse );

 

praneeth09m248
Calcite | Level 5

Hi Cau,

 

I have SAS/ETS and the code you suggested worked..

 

Is there a way to do the similar transformation but by excluding the current month.

 

Yt = Xt+1 + Xt+2 + Xt+3  ........................................Xt+12 /12

 

Currently, I am able to calculate

 

Yt = Xt + Xt+1 + Xt+3 ..............................................Xt+11/12

 

 

cau83
Pyrite | Level 9

Yes. If you wanted the next 12 months, make your window 13 instead of 12. Then, in a data step, do something like:

FwdAvg=(FwdAvg*13 - Total)/12;

This will temporarily get you the sum of the next 12 months + current month, then subtract current month and re-average.

praneeth09m248
Calcite | Level 5

Thanks cau, that works.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 4 replies
  • 2665 views
  • 0 likes
  • 2 in conversation