12 Month Forward Moving Average

[praneeth09m248]

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)

.

.

.

.

[cau83 · Accepted solution]

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]

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]

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]

Thanks cau, that works.