coding on forecast

[nyedetsy]

Hi Everybody,

I'm trying to do a report showing forecast for 2 different provinces, 3 different markets, data, including Results from 2014 and 2015, with Sale_Year and Sale_Month.

the OUT gives something similar to this.

PROVINCE	MRKT	PRODUCT	Sale_Year	Sale_Month	COUNT_of_sale_dt
ON	CORP. STORES	HP Comp	2014	1	588
ON	CORP. STORES	HP Comp	2014	2	492
ON	CORP. STORES	HP Comp	2014	3	616
ON	CORP. STORES	HP Comp	2014	4	643
ON	CORP. STORES	HP Comp	2014	5	565
ON	CORP. STORES	HP Comp	2014	6	599
ON	CORP. STORES	HP Comp	2014	7	759
ON	CORP. STORES	HP Comp	2014	8	802

here is the coding I'm using for the moment:

PROC SQL;

  CREATE TABLE work.QUERY_FOR_FILTER_FOR_TEST01 AS

  SELECT t1.PROVINCE,

          t1.MRKT,

            t1.Sale_Year,

          t1.Sale_Month,

  /* COUNT_of_sale_dt */

            (COUNT(t1.sale_dt)) AS COUNT_of_sale_dt

      FROM SASUSER.FILTER_FOR_TEST01_SALES_COH_0000 t1

      GROUP BY t1.PROVINCE,

              t1.MRKT_L2,

                t1.Sale_Year,

              t1.Sale_Month;

QUIT;

proc forecast data=SASUSER.QUERY_FOR_FILTER_FOR_TEST01 lead=12 out=prediction;

var Count_of_Sale_dt;

run;

I obtain this result attached.

How can I add coding which will give me the forecast per province/Market and Product as appearing bellow?

Anybody?

thanks.

[Reeza · Accepted solution]

Have you tried adding a BY statement with the relevant variables?

[udo_sas]

Hello -

Option 1: consider looking into SAS Forecast Server or SAS Forecasting for Desktop to get access to a more automated way of hierarchical forecasting projects.

Option 2: switch to PROC TIMESERIES for accumulating data into time series using different BY variables, then use your forecasting method of choice (for example exponential smoothing models as provided by PROC ESM) to create forecasts using a BY statement.

Downside of option 2: all series will be modeled with the same model assumptions - for example seasonal models. That's why option 1 will be your preferred choice, as very series will be automatically diagnosed and only appropriate models will be considered. Also option 1 will allow you to run several models and pick a winning model based on holdout samples.

Anyway - coming back to option 2 - here is an example using SASHELP.PRICEDATA for illustration purposes only:

proc sort data=sashelp.pricedata out=pricedata;

by regionname productname date;

run;

proc timeseries data=pricedata out=series;

id date interval=month accumulate=total;

var sale;

by regionname productname;

run;

proc esm data=series outfor=productforecast plot=forecasts lead=12;

id date interval=month;

forecast sale /model=addwinters;

by regionname productname;

run;

*results in 17 forecasts - one for each product per region;

proc sort data=sashelp.pricedata out=pricedata;

by regionname date;

run;

proc timeseries data=pricedata out=series;

id date interval=month accumulate=total;

var sale;

by regionname;

run;

proc esm data=series outfor=regionforecast plot=forecasts lead=12;

id date interval=month;

forecast sale /model=addwinters;

by regionname;

run;

*results in 3 forecasts - one for each region;

Hope this helps.

Thanks,

Udo

[HarshadMadhamshettiwar]

Hello Udo,

How can SAS (Base SAS- using coding) be used to generate automatic models, ie. without having user to choose(force fit) mode. ex. here we are applying exponential smoothing family of models irrespective of the series is.

Thanks.

Harshad M.

[udo_sas]

Hello -

There is no such functionality available in base SAS out of the box - my suggestion is to consider option 1.

Having said this, there are lots of things people have archived with macro coding - but it won't be a simple task.

Thanks,

Udo

[nyedetsy]

one more thing,

if in the coding I add   /* mdy(t1.Sale_Month,1,t1.Sale_Year) as date1, */

to merge Month and Year...how can I do to have my dates appearing on my viwetable in this format MMYY. I have the SAS date in giving me SAS format 19724 instead.

can you help me insert the right coding?

PROC SQL;

  CREATE TABLE work.QUERY_FOR_FILTER_FOR_TEST01 AS

  SELECT t1.PROVINCE,

          t1.MRKT,

            t1.Sale_Year,

          t1.Sale_Month,

mdy(t1.Sale_Month,1,t1.Sale_Year) as date1,

  /* COUNT_of_sale_dt */

            (COUNT(t1.sale_dt)) AS COUNT_of_sale_dt

      FROM SASUSER.FILTER_FOR_TEST01_SALES_COH_0000 t1

      GROUP BY t1.PROVINCE,

              t1.MRKT_L2,

                t1.Sale_Year,

              t1.Sale_Month;

QUIT;

thank you.

proc forecast data=SASUSER.QUERY_FOR_FILTER_FOR_TEST01 lead=12 out=prediction;

var Count_of_Sale_dt;

run;

[alexchien]

you can try the following in the SQL statement

mdy(t1.Sale_Month,1,t1.Sale_Year) as date1 format = mmyy.