ARIMA Looping and Using AIC for Model Selection

[BCNAV]

Hey all. I am just using a quick and dirty loop for choose and ARIMA (AR for now, will add the MA later) for identifying potential models. I will use AIC as a model selection criteria. The program I have will get SAS output from the ARIMA command, then copy only the AIC to another dataset, then loop back and start over. I can then look at the individual outputs and see the results. I would like to be able to append the results as I go to create one data file with all the AIC's and orders in them. I am stuck on how to append the individual datasets into one. Can appends be done dynamically, or must they be done at the end? Since each AIC dataset is called AIC_1, AIC_2, etc., am I able to just append them all at the end some how using pattern matching on AIC_*?

Here is my code so far:

%macro forecast_arima_auto;

/*

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 ar;

%do ar=1 %to 4;

     proc arima data=EGTASK.NAV_TIME_SERIES;

     where Date <= &hist_date;

     identify var=OV_ECU_M11_SA(1) stationarity=(adf=10); run;

     estimate p=&ar maxiter=100 method=ml outstat=egtask.AR_&ar; run;

     data AIC_&ar;

           set EGTASK.AR_&ar;

           where _STAT_='AIC';

           drop _TYPE_;

           AR=&ar; run;

%end;

%mend;

%forecast_arima_auto;

Thanks for any help

-Bill

[alexchien]

you can use proc append to append the results in one table. For example,

proc append base = AIC data = EGTASK.AR_&ar (where = (_STAT_='AIC')  drop =  _TYPE_) force;

run;

You need to make sure to delete the base table if exists before the loop starts.

thanks

Alex

[BCNAV]

Thank you. I had figured out the append line and used:

proc append base=EGTASK.AR_AIC data=EGTASK.AIC_&ar;

The question I have is, how do you check if the base exists before the loop and if it does delete it. SAS will not make the appended file if it exists for some reason (no overwrite possible?)

thanks

[alexchien · Accepted solution]

you can add the following code to your macro to drop the base table at the beginning of the loop.

%if %sysfunc(exist(EGTASK.AR_AIC)) %then %do;
    proc sql;
        drop table EGTASK.AR_AIC;
    quit;
%end;

[BCNAV]

All works! Thanks!