topic Re: Seasonal adjustment for daily/weekly data in SAS Forecasting and Econometrics

Seasonal adjustment for daily/weekly data

lizzy28 — Mon, 17 Aug 2015 16:17:37 GMT

Hi all,

I have daily sales data which display strong weekly seasonality as well as monthly seasonality. It means that there was spike at the end of each week and greater spike at the end of each month. To adjust the aggregated weekly data for their seasonality, can I use X12 procedure as monthly data?

Thank you as always!

Lizi

Re: Seasonal adjustment for daily/weekly data

udo_sas — Tue, 18 Aug 2015 12:46:06 GMT

Hello -

If you have access to SAS Forecast Server you may consider the following approach:

Role up the data to weekly frequency and use a seasonal model to capture end of month spikes (low-frequency forecast)
Model daily data to capture week seasonal behavior (such as high demand on weekends) (high-frequency forecast)
Use the HPFTEMPRECON procedure to "align" 1) and 2). HPFTEMPRECON reconciles the high-frequency forecasts to the low-frequency forecasts in such a way that the accumulation of the reconciled high-frequency forecasts is equal to the low-frequency forecasts.

You may find this thread useful, too: https://communities.sas.com/message/151039

Thanks,

Udo

Re: Seasonal adjustment for daily/weekly data

lizzy28 — Tue, 18 Aug 2015 21:00:56 GMT

Thanks, Udo!

Unfortunately I don't have SAS Forecast Server. I have looked into the HPFTEMPRECON procedure, and it looks that it only works in the Forecast Server. Would there be other way that works in the regular environment?

Thanks again!

Lizi

Re: Seasonal adjustment for daily/weekly data

udo_sas — Wed, 19 Aug 2015 14:17:29 GMT

Hi Lizi -

Many many time series are we talking about?

Thanks,

Udo

Re: Seasonal adjustment for daily/weekly data

lizzy28 — Wed, 19 Aug 2015 16:13:15 GMT

Udo, there are only two-year daily sales data, which have decent weekly and monthly seasonality.

Thanks,

Lizi

Re: Seasonal adjustment for daily/weekly data

udo_sas — Wed, 19 Aug 2015 22:06:36 GMT

Lizi -

I should have been clearer in my question: I was wondering how many products (or sales) you are trying to forecast. Are we talking about 1 product, 10s, 100s, 1000s, more?

In general having only 2 years of daily data will be a challenge - as you observed each day only twice at max. So depending on your patterns coming up with accurate models might be difficult.

Thanks,

Udo

Re: Seasonal adjustment for daily/weekly data

lizzy28 — Thu, 20 Aug 2015 12:49:40 GMT

Thanks, Udo.

Right now I am only looking at one product. I thought about the short time period I am looking at as well. But unfortunately the project is to find how marketing campaign helps predict retail sales, and we only have two-year marketing campaign data. I have to just use less data points.

I tried vector autoregression model with unadjusted data and hope to run the model again after the data are seasonally adjusted.

Thank you!

Lizi

Re: Seasonal adjustment for daily/weekly data

udo_sas — Thu, 20 Aug 2015 16:18:45 GMT

Hello -

If it is only one series you are dealing with you don't need to worry about HPFTEMPRECON, as it is designed to handle large hierarchical projects.

One approach might be to do the following:

First model the daily forecast - which will most likely not capture the end of end of month spikes
Look at the residuals of the daily forecast - and based on the residuals create a new data set which only includes your end of week period - for example you may say the end of month peak always occurs 5 days before and 3 days after the true end date of a month (for example August 31st). Set all other periods to 0.
Create a custom interval which captures your end of month behavior (se: http://support.sas.com/documentation/cdl/en/etsug/63348/HTML/default/viewer.htm#etsug_intervals_sect008.htm)
Model the end of month residuals using your custom interval using a seasonal model - the custom interval will take care of the "off-season" periods.
Finally adjust your daily forecast with the end of month forecast - for example simple add the uplift to your daily forecast
This white paper illustrates a similar approach: http://www.sas.com/offices/latinamerica/chile/emailmkt/forecast/pdf/Forecasttimecompression.pdf

Thanks,

Udo

Re: Seasonal adjustment for daily/weekly data

lizzy28 — Thu, 20 Aug 2015 16:27:31 GMT

You are so awesome, Udo!

I'll try it out and let you know.

Thanks a bunch!

Lizi

Re: Seasonal adjustment for daily/weekly data

lizzy28 — Mon, 24 Aug 2015 19:18:17 GMT

Hi Udo,

One more question for the adjustment:

In the second step "2. Look at the residuals of the daily forecast...", create a new data set that only includes the end of week period. Then your example talked about the end of month peak. Also, the following steps, 4 and 5, they are about adjusting the forecast with end of month forecast. Should the example and the following steps be about adjusting the forecast with end of week forecast, rather than end of month forecast?

I thought that the process would be to adjust with end of week forecast and then adjust with end of month forecast.

Sorry for endless question.

Thanks a lot!

Lizi

Re: Seasonal adjustment for daily/weekly data

udo_sas — Mon, 24 Aug 2015 21:30:31 GMT

Hi Lizi -

Of course, I should have been more careful in my description - sorry for that.

Please read my response in combination with my earlier response:

Model daily data to capture week seasonal behavior (such as high demand on weekends) (high-frequency forecast)
Role up the data to weekly frequency and use a seasonal model to capture end of month spikes (low-frequency forecast)

Thanks,

Udo

Re: Seasonal adjustment for daily/weekly data

lizzy28 — Thu, 27 Aug 2015 16:21:28 GMT

Hi Udo,

Continuing from my previous ask, I was wondering whether I am supposed to use seasonally adjusted data series, i.e. forecast of sales with residual forecasts of spikes, in a vector autoregression model (VAR) or any regression analysis.

The purpose of doing seasonal adjustment is to get better prediction in a VAR. Please advice.

Thanks again!

Lizi