BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
nyedetsy
Calcite | Level 5

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;

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

View solution in original post

6 REPLIES 6
Reeza
Super User

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

udo_sas
SAS Employee

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
Obsidian | Level 7

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
SAS Employee

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
Calcite | Level 5

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
Pyrite | Level 9

you can try the following in the SQL statement

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 1546 views
  • 0 likes
  • 5 in conversation