topic Re: Merging Forecast Tables in SAS Programming

Merging Forecast Tables

BCNAV — Wed, 14 Jun 2017 16:54:15 GMT

Hi all. I am using the following program to generate simple forecasts for airport data. It all works fine and end up with all I need. Specifially, I use the sql commands to generate a table of history and forecasts (by month) for each airport code. It occured to me that it would be easier for exporting and other analysis if I could merge each forecast on the fly in the do loop to a MERGE_FCST table and have all forecasts in one spot (then delete the temporary tables). I cannot figure out out to accomplish this within my program. Each of the individual tables has a date column and a forecast column. I would like to merge all forecasts into one MERGE_FCST table with one date column and then the airport history/forecast column side by side. Thanks for any help.

Bill

%macro forecast_hw (airport_list=);

Macro language does not use quotes around the values of macro variables

Macro language %DO loops are limited, and can iterate over a range of numeric values only

ods tagsets.sasreport13(id=EGSR) gtitle gfootnote; /* This ensures titles go into graph area */

%let hist_date='01apr2017'd;

%let length_fcst=12;

%local n i;

%do n=1 %to %sysfunc(countw(&airport_list));

%let i=%scan(&airport_list,&n);

/* --------------------------------------- FORECASTING --------------------------------------- */

/* Do regular HW Model for forecasting */

proc forecast data=EGTASK.AIRPORT8_TERM_TS method=winters interval=month trend=2 alpha=0.05 seasons=month lead=&length_fcst nstart=max nsstart=max out=FCST_Winters_Auto_&i outest=Est_Winters_Auto_&i;

id date;

var &i._TCU8;

where date <= &hist_date; run;

/* Do STEPAR Model for forecasting with linear trend */

proc forecast data=EGTASK.AIRPORT8_TERM_TS method=stepar interval=month trend=2 alpha=0.05 lead=&length_fcst nlags=13 out=FCST_Stepar_LT_&i outest=Est_Stepar_LT_&i;

id date;

var &i._TCU8;

where date <= &hist_date; run;

/* Do STEPAR Model for forecasting with quadratic trend */

proc forecast data=EGTASK.AIRPORT8_TERM_TS method=stepar interval=month trend=3 alpha=0.05 lead=&length_fcst nlags=13 out=FCST_Stepar_QT_&i outest=Est_Stepar_QT_&i;

id date;

var &i._TCU8;

where date <= &hist_date; run;

/* --------------------------------------- CREATE HISTORY & FORECASTS --------------------------------------- */

proc sql;

create table egtask.hist_fcst_HW_&i as

select date,&i._TCU8 from EGTASK.AIRPORT8_TERM_TS

outer union corr

select date,&i._TCU8 from WORK.FCST_Winters_Auto_&i; run;

proc sql;

create table egtask.hist_fcst_STEPLIN_&i as

select date,&i._TCU8 from EGTASK.AIRPORT8_TERM_TS

outer union corr

select date,&i._TCU8 from WORK.FCST_Stepar_LT_&i; run;

%end;

%mend;

%forecast_hw (airport_list=CYVR CYOW CYYZ CYUL);

Re: Merging Forecast Tables

ballardw — Wed, 14 Jun 2017 17:18:47 GMT

If you are talking about combining data sets from this point in your code:

proc sql;
           create table egtask.hist_fcst_HW_&i as
                select date,&i._TCU8 from EGTASK.AIRPORT8_TERM_TS
                outer union corr
                select date,&i._TCU8 from WORK.FCST_Winters_Auto_&i; run;
proc sql;
           create table egtask.hist_fcst_STEPLIN_&i as
                select date,&i._TCU8 from EGTASK.AIRPORT8_TERM_TS
                outer union corr
                select date,&i._TCU8 from WORK.FCST_Stepar_LT_&i; run;

%end;

%mend;

I might suggest addint two data steps at AFTER the %end of the loop that look like

%end;
data  egtask.hist_fcst_HW_Final;
  set  egtask.hist_fcst_HW_: ;
run;

data  egtask.hist_fcst_STEPLIN_final;
  set  egtask.hist_fcst_STEPLIN_: ;
run;
%mend;

The : at the end of the base of your file name says to set all of the data sets starting with that name.

Re: Merging Forecast Tables

BCNAV — Wed, 14 Jun 2017 18:26:43 GMT

Thanks. Unfortunately that just appends the tables to the existing one. I am hoping to get the following:

I have files for each airport code that look like (one table per airport code, so if I have 30 airports I have 30 tables):

Date CYOW_HF

I would like to merge these automaticalling in the loop to end up with:

Date CYOW_HF CYYZ_HF CYOW_HF, etc.

Using the : way just appends the files to the end of each other with multiple date fields and the data not spread straight across.

Thanks!

Re: Merging Forecast Tables

ballardw — Wed, 14 Jun 2017 19:46:25 GMT

@BCNAV wrote:

Thanks. Unfortunately that just appends the tables to the existing one. I am hoping to get the following:

I have files for each airport code that look like (one table per airport code, so if I have 30 airports I have 30 tables):

Date CYOW_HF

I would like to merge these automaticalling in the loop to end up with:

Date CYOW_HF CYYZ_HF CYOW_HF, etc.

Using the : way just appends the files to the end of each other with multiple date fields and the data not spread straight across.

Thanks!

The use of those data set names looks like the process could well be done as a by group and not have to loop through anything.

You can use the same : approach on a MERGE statement.

If you are trying to split one result then look up thread on the fourm.

If that doesn't work you'll need to provide some data.

I do think that the whole approach may be a bit over complicated as having an AIRPORT variable and all of the analysis varaibles with the same name insted of names like CYVR_tcu8 CYOW_tcu8 instead

Airport tcu8

CYVR (value)

CYOW (value)

Then one forecast with BY Airport;

If you want a final report that reads with the airports across at the end then Proc Report or Tabulate will do that fine and the whole process is much easier.