Hello everyone. I have many variables to seasonal adjust, and then merge into one dataset. I can use the attached code, but then I need to use EG to merge them alltogether into a time series database. This is a pain in EG and I'm hoping it can be done within the maco code automatically. The first datastep sets up the data file I want to use, then as the macro goes, I would like to place the seasonally adjusted series into that database. I am stuck as to how to automatically update the database file after every loop iteration. Any suggestions are welcome.  Thanks!   data EGTASK.NAV_TIME_SERIES_ALL_SA_TS; set EGTASK.NAV_TIME_SERIES_ALL_TS (keep=date year month index); run; %macro seasonal_adj (sa_list=); %local n i; %do n=1 %to %sysfunc(countw(&sa_list)); %let i=%scan(&sa_list,&n); proc x11 data=EGTASK.NAV_TIME_SERIES_ALL_TS noprint; monthly date=Date; var &i; output out=&i._sa A1=&i /* D10 is really multiplied by 100, so divide these by 100 in a spreadsheet */ D10=&i._SF D11=&i._SA; run; %end; %mend; %seasonal_adj (sa_list=TERM_ECU TERM_ECU_M11 TERM_ECU_M22 TERM_ECU_M33 TERM_ECU_M34 TERM_ECU_M35 TERM_AMT TERM_AMT_M11 TERM_AMT_M22 TERM_AMT_M33 TERM_AMT_M34 TERM_AMT_M35 ENR_ECU ENR_ECU_M11 ENR_ECU_M22 ENR_ECU_M33 ENR_ECU_M34 ENR_ECU_M35 ENR_AMT ENR_AMT_M11 ENR_AMT_M22 ENR_AMT_M33 ENR_AMT_M34 ENR_AMT_M35 OV_ECU OV_ECU_KTOK OV_ECU_M11 OV_ECU_M14 OV_ECU_M22 OV_ECU_M23 OV_ECU_M24 OV_ECU_M45 OV_ECU_M46 OV_ECU_OTHER_SUM OV_AMT OV_AMT_KTOK OV_AMT_M11 OV_AMT_M14 OV_AMT_M22 OV_AMT_M23 OV_AMT_M24 OV_AMT_M45 OV_AMT_M46 OV_AMT_OTHER_SUM); ... View more

I think this has been asked before, but has SAS implemented Holt Winters Damped Trend with Multiplicative Seaonsality yet?   Thanks   ... View more

I am using a macro do loop to run numerous Holt-Winters forecasts. I would like to combine the estimates into one table as the loop runs. Each of the 20 forecasts produces output of the following:   _TYPE_     Date       CYVR_TCU8 N          2017APR    208 NRESID     2017APR    208 DF         2017APR    194 WEIGHT1    2017APR    0.1055728 WEIGHT2    2017APR    0.1055728 WEIGHT3    2017APR    0.25 SIGMA      2017APR    13109.328 CONSTANT   2017APR    394060.29 LINEAR     2017APR    2039.1086 S_JAN      2017APR    0.9584237 S_FEB      2017APR    0.8701241 S_MAR      2017APR    0.9710871 S_APR      2017APR    0.9186076 S_MAY      2017APR    1.0205678 S_JUN      2017APR    1.0751503 S_JUL      2017APR    1.1659275 S_AUG      2017APR    1.1619254 S_SEP      2017APR    1.0370984 S_OCT      2017APR    0.9766547 S_NOV      2017APR    0.8751276 S_DEC      2017APR    0.9693058 SST        2017APR    2.9012E11 SSE        2017APR    3.334E10 MSE        2017APR    171854482 RMSE       2017APR    13109.328 MAPE       2017APR    2.9196567 MPE        2017APR    0.1482134 MAE        2017APR    9568.9117 ME         2017APR    749.04853 RSQUARE    2017APR    0.8850831   So after 20 runs I will get 20 of these outputs, with the only thing changing is the far right column, it will change depending on the airport code (e.g., CYYX_TCU8). I would like to create a table as the loop runs so that the final output will have _TYPE_  DATE  and then all forecast statsitics for each airport. I use the varaible n to loop for each airport in the macro and it works. So I tried to use the n as follows:   %if &n=1 %then %do; data egtask.est_hw; set Est_HW_Auto_&i; run; %end; %else %do; proc sql; alter table egtask.est_hw add &i._TCU8 numeric; run; update egtask.est_hw set &i._TCU8=(select &i._TCU8 from Est_HW_Auto_&i); run; %end;   I first set up the initial table when the first forecast (n=1) is done. Past n=1 I want to add the airport forecasts statistics column only from each forecast to this new table. So I update the combined table called egtask.est_hw by copying the statistics from each forecast run (Est_HW_Auto_&i). I am getting the error "ERROR: Subquery evaluated to more than one row."   I am hoping to get a final table I can review quickly that would look like:   _TYPE_ Date CYVR_TCU8 CYYZ_TCU8 CYOW_TCU8 N 2017APR 208 . . NRESID 2017APR 208 . . DF 2017APR 194 . . WEIGHT1 2017APR 0.1055728 . . WEIGHT2 2017APR 0.1055728 . . WEIGHT3 2017APR 0.25 . . SIGMA 2017APR 13109.328 . . CONSTANT 2017APR 394060.29 . . LINEAR 2017APR 2039.1086 . . S_JAN 2017APR 0.9584237 . . S_FEB 2017APR 0.8701241 . . S_MAR 2017APR 0.9710871 . . S_APR 2017APR 0.9186076 . . S_MAY 2017APR 1.0205678 . . S_JUN 2017APR 1.0751503 . . S_JUL 2017APR 1.1659275 . . S_AUG 2017APR 1.1619254 . . S_SEP 2017APR 1.0370984 . . S_OCT 2017APR 0.9766547 . . S_NOV 2017APR 0.8751276 . . S_DEC 2017APR 0.9693058 . . SST 2017APR 2.90E+11 . . SSE 2017APR 3.33E+10 . . MSE 2017APR 171854482 . . RMSE 2017APR 13109.328 . . MAPE 2017APR 2.9196567 . . MPE 2017APR 0.1482134 . . MAE 2017APR 9568.9117 . . ME 2017APR 749.04853 . . RSQUARE 2017APR 0.8850831 . .   Of course, all would be populated properly and there would be 20 airport model statistics as well.   Thanks for any help -Bill ... View more

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);   ... View more