## coding on forecast

Solved
Occasional Contributor
Posts: 10

# coding on forecast

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.

PROVINCEMRKTPRODUCTSale_YearSale_MonthCOUNT_of_sale_dt
ONCORP. STORESHP Comp20141588
ON
 CORP. STORES
HP Comp20142492
ON
 CORP. STORES
HP Comp20143616
ON
 CORP. STORES
HP Comp20144643
ON
 CORP. STORES
HP Comp20145565
ON
 CORP. STORES
HP Comp20146599
ON
 CORP. STORES
HP Comp20147759
ON
 CORP. STORES
HP Comp20148802

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;

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.

Accepted Solutions
Solution
‎08-26-2015 01:06 PM
Super User
Posts: 19,822

## Re: coding on forecast

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

All Replies
Solution
‎08-26-2015 01:06 PM
Super User
Posts: 19,822

## Re: coding on forecast

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

SAS Employee
Posts: 416

## Re: coding on forecast

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;

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;

by regionname;

run;

*results in 3 forecasts - one for each region;

Hope this helps.

Thanks,

Udo

Contributor
Posts: 37

## Re: coding on forecast

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.

SAS Employee
Posts: 416

## Re: coding on forecast

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

Occasional Contributor
Posts: 10

## Re: coding on forecast

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.

var Count_of_Sale_dt;

run;

SAS Super FREQ
Posts: 93

## Re: coding on forecast

you can try the following in the SQL statement

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

🔒 This topic is solved and locked.