Calcite | Level 5

## 12 Month Forward Moving Average

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

## Re: 12 Month Forward Moving Average

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.

"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 );`

4 REPLIES 4
Pyrite | Level 9

## Re: 12 Month Forward Moving Average

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.

"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 );`

Calcite | Level 5

## Re: 12 Month Forward Moving Average

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

Pyrite | Level 9

## Re: 12 Month Forward Moving Average

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.

Calcite | Level 5

## Re: 12 Month Forward Moving Average

Thanks cau, that works.

Discussion stats
• 4 replies
• 2684 views
• 0 likes
• 2 in conversation