turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- Analytics
- /
- Forecasting
- /
- Moving average forecast

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-30-2014 03:07 AM

Hi,

As a part of my forecast I am using a moving average based on three observations. Calculating this in SAS I have managed do it only for outcome data and not managed to do it for forecast data. The moving average for a specific month should be the average for the same months three years back. I have tried different kind of syntax but I have found nothing that makes an correct calculation for values after May 2014 (my last outcome).

This syntax creates correct values up until May 2014. After that everything is blank (I have created MA after that in several ways, but never correct).

proc expand data=QQQ out=QQQQ;

id year

by month

convert UUU=UUU_LAG

III=III_LAG

...../

transformout=(reverse movave 3 reverse);

run;

Any ideas/functions? I think it should work from this setup.

Accepted Solutions

Solution

09-04-2014
07:10 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-04-2014 07:10 PM

Hello Laasse -

Yes, my data step was only used for creating an example data set.

I have modified your code to address your additional question (see below).

Thanks,

Udo

proc timedata data=have out=_null_ outarray=want;

id manad interval=MONTH;

var SGI_ROD_FP_ANDEL;

outarray movavg;

do t = 1 to _LENGTH_;

movavg

if missing(SGI_ROD_FP_ANDEL

end;

run;

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-30-2014 05:42 AM

Proc expand is rather used to transform data than to use it for forecasting. If you#re actually looking for simple moving averages (not exponentially weighted ones) you could use a data step. Maybe something like this:

Data A;

Input Dateate9. ACTUAL;

Format Date Date9.;

Datalines;

01JAN2000 23

01FEB2000 45

01MAR2000 12

01APR2000 89

01MAY2000 23

;

Run;

Data A_Forecast (Drop=dummy);

Retain dummy;

Set A;

dummy=Sum(dummy,ACTUAL,-Lag3(ACTUAL));

Mov_Ave_3_GD=dummy/3;

Run;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-30-2014 05:47 AM

p.s. credit goes to SAS :-)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-30-2014 09:19 AM

I have seen that kind of solution. The problem however is that my MA is not as simple as that one (they are still simple but not enough..). For June 2014 I want the average of June 2011-2013. And so on, thus I don´t just want the average of the three last months. How can I add a by statement and an ID variable into your solution?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-30-2014 09:33 AM

Give us an example to illustrate your problem .

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-30-2014 10:25 AM

I might be entirely wrong, but I think:

%Let Periods=3;

%Let Lead=5;

%Let Multiplier=12; /* 12 months */

%Let Comb_Lag=%Eval(&Periods.*&Multiplier.);

Data A (Drop=i j k);

Format Date Date9.;

Do k=1 to 3;

Do j=1 to 5;

Do i=1 to 12;

Date=MDY(i,1,j+2000);

ACTUAL=Round(Normal(1)*k+20); /* k as Standard Deviation */

ID=k;

Output;

End;

End;

End;

Run;

/* "simple" seasonal (??) moving average */

Data A_Forecast (Keep=ID Date ACTUAL Mov_Ave);

Set A;

By ID;

Array dummy{*} dummy1-dummy12;

Array dummy_sum{*} dummy_sum1-dummy_sum12;

Array dummy_drop {*} dummy_drop1-dummy_drop12;

Retain dummy_sum1-dummy_sum12;

Do i=1 To 12;

If Month(Date) eq i Then Do;

dummy{i}=ACTUAL;

dummy_drop{i}=Lag&Comb_Lag.(ACTUAL);

End;

End;

If First.ID Then Do;

count=0;

Do i=1 To 12;

dummy_sum{i}=0;

End;

End;

count+1;

If count gt &Comb_Lag. Then Do;

Do i=1 To 12;

dummy_sum{i}=Sum(dummy_sum{i},dummy{i},-dummy_drop{i});

End;

End;

Else Do;

Do i=1 To 12;

dummy_sum{i}=Sum(dummy_sum{i},dummy{i});

End;

End;

If count ge &Comb_Lag. Then Do;

Do i=1 To 12;

If not Missing (dummy{i}) Then dummy_sum_act=dummy_sum{i};

End;

Mov_Ave=dummy_sum_act/&Periods.;

End;

Run;

/* fill in lead */

Data A_Forecast_Lead (Drop=i);

Retain Date ID Mve_Ave;

Set A_Forecast;

By ID;

If Last.ID Then Do;

Output;

Do i=1 to &Lead.;

Date=IntNX('month',Date,1,'same');

ACTUAL=.;

Output;

End;

End;

Output;

Run;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-04-2014 04:53 AM

Thank you user24 feb. I will however start by looking at udo@sas' answer and method.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-31-2014 03:46 AM

My summer vacation starts today (earlier than planned...). I will be back in about two and half week and then try to solve this. I appreciate your help!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-31-2014 02:54 PM

Hello -

After returning from vacation you may want to look into PROC TIMEDATA to accomplish this task.

Assuming that you have access to 13.1 of course and I have understood your request properly.

See example below.

Thanks,

Udo

data have;

set sashelp.air end=last;

if last then do i=0 to 12;

date=intnx('month',"01DEC60"d, i);

if i ge 1 then air=.;

output;

end;

else output;

drop i;

run;

proc timedata data=have out=_null_ outarray=want;

id date interval=MONTH;

var air;

outarray movavg;

do t = 1 to _LENGTH_;

movavg

end;

run;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-04-2014 04:50 AM

Thank you udo@sas. I couldn´t really start with this after returning from my vacation, but now I can find some time and I have already found some use of your answer. However I'm not there yet. I think I don't need your kind of data step because I already have a date variable manad (YYMMN6. 200801-201812) and of course my variable of interest SGI_ROD_FP_ANDEL (with values from 200801 until 201405). When writing my proc timedata step I'm doing like this:

proc timedata data=have out=_null_ outarray=want;

id manad interval=MONTH;

var SGI_ROD_FP_ANDEL;

outarray movavg;

do 1 to _LENGTH_;

movavg

end;

run;

Then I got movavg values from 201101 until 201505. My objective is however to get values from 201406 until 201812. Hence I want moving average values that depend of a mix of SGI_ROD_FP_ANDEL values and movavg values and some that only depends of movavg values. Is that possible? When I substitute "_LENGTH_" for something else, it simply doesn't work. What I'm doing wrong?

Solution

09-04-2014
07:10 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-04-2014 07:10 PM

Hello Laasse -

Yes, my data step was only used for creating an example data set.

I have modified your code to address your additional question (see below).

Thanks,

Udo

proc timedata data=have out=_null_ outarray=want;

id manad interval=MONTH;

var SGI_ROD_FP_ANDEL;

outarray movavg;

do t = 1 to _LENGTH_;

movavg

if missing(SGI_ROD_FP_ANDEL

end;

run;