topic Re: PROC HPFTEMPRECON -- week to month reconciliation? in SAS Forecasting and Econometrics

PROC HPFTEMPRECON -- week to month reconciliation?

Mr_Sosa — Mon, 06 May 2013 13:41:23 GMT

All,

I am currently trying to leverage PROC HPFTEMPRECON reconcile weekly forecasts to a monthly forecast. The literature I have read states that week to month reconciliation is not possible ( e.g., ID <variable> interval = week, BENCHID <variable> interval = month) due to the fact that weeks are not always nested within months. This is unlike reconciling quaters to the year (e.g., ID <variable> interval = QTR, BENCHID <variable> interval = YEAR) which is possible because quarters are fully nested in a year. I have tried to "force" the reconciliation by using an ID interval = WEEKV.7 and BENCHID interval = R445MON but the error states the following: ERROR: ID interval is not nested in BENCHID interval. Attempted use not currently supported.

Does anyone know if there is a way to accomplish this task using HPFTEMPRECON? For instance, is it possible to create custom time intervals for the week and month that would reconcile to each other?

Thank you for the help!

Re: PROC HPFTEMPRECON -- week to month reconciliation?

udo_sas — Mon, 06 May 2013 17:31:57 GMT

Hello -

This paper: http://support.sas.com/resources/papers/proceedings11/326-2011.pdf provides more details on using HPFTEMPRECON and its limitations. In particular the authors point out that weeks don't have to be fully nested in months, as a week can span two months. Therefore, the frequency of the indicator series cannot be weekly when the benchmark series has a monthly frequency.

One strategy for overcoming this issue for point forecasts (i.e. PREDICT) is as follows:

Use PROC EXPAND to generate daily forecasts from the weekly forecasts
Use PROC HPFTEMPRECON to reconcile the daily forecasts to the monthly forecasts
Accumulate the reconciled daily forecasts to the weekly interval using PROC TIMESERIES

Hope this is useful.

Thanks,

Udo

Re: PROC HPFTEMPRECON -- week to month reconciliation?

Mr_Sosa — Mon, 06 May 2013 18:48:06 GMT

Udo,

Thank you for the reply. I will take a look into this as a possible solution. I should have mentioned this in my earlier post, but I need to reconcile weekly and monthly FISCAL data. As I am sure you are aware, in the retail industry, we are using a fiscal calendar. So for instance, April 1st 2013 might actually fall in the fiscal month of March 2013 (the 5th week of the fiscal month of March to be exact). Would the method you recommended account for this, or would my BENCHID interval need to be something like R445MON rather than MONTH to account for this? Again, thank you for your input on this issue!

Re: PROC HPFTEMPRECON -- week to month reconciliation?

udo_sas — Tue, 07 May 2013 17:12:25 GMT

Hello -

This is an example for illustration purposes only - using your data using temporal reconciliation might turn out to be more useful.

The challenge is to get the boundaries of the different aggregation levels right (otherwise you might end up with not fully observed intervals).

As you were wondering about using custom intervals: HPFTEMPRECON currently does not suggest custom intervals.

Thanks,

Udo

*create sample data;

data subset(keep=date volume);

set sashelp.timedata;

date=datepart(datetime);

if date lt "01JAN1998"d or date gt "31DEC99"d then delete;

format date date9.;

run;

proc timeseries data=subset out=subset;

id date interval=week accumulate=total;

var volume;

run;

*forecast on weekly frequency;

proc hpfengine data=subset rep=sashelp.hpfdflt globalselection=tsfsselect out=_null_ outfor=outforwk plot=forecastslead=5;

id date interval=week accumulate=total;

forecast volume;

run;

*forecast on monthly frequency - using R445;

proc hpfengine data=subset rep=sashelp.hpfdflt globalselection=tsfsselect out=_null_ outfor=outformon plot=forecastslead=1;

id date interval=R445MON accumulate=total;

forecast volume;

run;

*convert weekly predictions to daily;

proc expand data=outforwk(keep=_name_ actual predict date) out=outforday from=week to=day;

id date;

convert predict;

by _name_;

run;

*temporal reconcilation of month to day;

proc hpftemprecon data=outforday benchdata=outformon outfor=benfor;

id date interval=day;

benchid date interval=R445MON;

run;

*aggregate adjusted daily forecast to weekly frequency;

proc timeseries data=benfor out=series;

id date interval=week accumulate=total;

var predict;

run;

*merge results of weekly forecasts and adjusted weekly forecasts and plot;

data result;

merge outforwk series(rename=(predict=bench));

by date;

run;

proc sgplot data=result;

series x=date y=actual;

series x=date y=predict;

series x=date y=bench;

run;