topic Re: Reconcile a hierarchical forecast using a weighted average? in SAS Forecasting and Econometrics

Reconcile a hierarchical forecast using a weighted average?

Joel_Wesley_NBCU — Tue, 20 Oct 2015 14:46:13 GMT

Hello,

We're trying to create a forecast with bottom-up reconciliation - half-hour rolled up to day, days rolled up to weeks, etc. The problem is, every half hour might have a different amount of treatment within it. So rather than simply adding up all the half hour results to get the results for a day, we'd like to weight each half hour by the amount of treatment in that half hour. Is there a simple way to do this in Forecast Studio?

Thanks,

Joel

Re: Reconcile a hierarchical forecast using a weighted average?

mitrov — Wed, 21 Oct 2015 19:43:48 GMT

Hi Joel,

Unfortunately that is not possible withing the FS UI.

However, being that you are using Bottom-Up, it is fairly simple to implement it in coding. It would require a few calls to PROC TIMESERIES to accumulate the lowest-level forecasts to each higer-level of the hierarchy. You can look at the code that generates the data for the project as an example.

For example, if you open "CREATE_PROJECT_IMPORT_DATA.sas" in your your project folder, look at the code that follows this comment:

*----------------------------------------------------------------------
* level: _top
*---------------------------------------------------------------------;
There will be one run of PROC TIMESERIES for each level of the hierarchy. You need to replace the lowest-level data with the lowest-level forecasts, e.g. replace "data=_HPF2.DATA" with "data=_HPF2.OUTFOR", where "_HPF2" is the library that contains your lowest level of the hierarchy.

Then you will need a VAR statement to accumulate your PREDICT variable, e.g.

var predict /acc= AVERAGE ;

Store the results in a data set using the OUT= option on the proc statement. This will give you the accumulated prediction for each level of the hierarchy. Since now each level is generated directly from the lowest level, it will be properly weighted by the number of observations in each group.

If you have questions feel free to contact our tech support and we will assist you.

Best regards,

Michele

Re: Reconcile a hierarchical forecast using a weighted average?

mitrov — Wed, 21 Oct 2015 21:29:24 GMT

I should have added that, once you have your code ready, you can use it to create a report which can be run within the UI.

Re: Reconcile a hierarchical forecast using a weighted average?

Joel_Wesley_NBCU — Thu, 22 Oct 2015 13:02:32 GMT

That was helpful. Thank you!