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);
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.
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!
@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.
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.