SAS ODS Sandwich in PROC ARIMA with BY statement generate strange results

Question

I'm trying to export a bunch of stationarity tests to both HTML and Excel. With PROC ARIMA I'm getting strange behaviour when I try to wrap each part in an "ODS Sandwich". It appears this is due to using the BY var command in PROC ARIMA. When BY is not used I get the expected/desired ODS output.

When I CLOSE an ODS "sandwich" the preceeding ARIMA output is dropped from the file I try to send it to. It is instead saved in the subsequent ODS destination that I open. The result is the final ODS ARIMA output is lost and each file is incorrectly named and located.

The dataset abc is produced at the bottom. Here is the reproducible commands that are failing for me.

/*  =========================================== */
/*          PROC ARIMA for Stationarity
                  LEVELS                        */
/*  =========================================== */

ODS _ALL_ CLOSE;

%let pt    = 1;
%let ARpt1 = LEVELS;
ODS graphics on / reset= INDEX imagename="&ARpt1.";

filename X_ADF&pt. "&outDir.&ARIMAdir.ARIMA_ADF_&ARpt1..xlsx" ;
filename H_ADF&pt. "&outDir.&ARIMAdir.ARIMA_ADF_&ARpt1..html" ;
filename g_ADF&pt. "&outDir.&ARIMAdir." ;

ods html  (id=&pt.) file= H_ADF&pt.    gpath = g_ADF&pt.;
ods EXCEL (id=&pt.) file = X_ADF&pt.  
    options(SHEET_INTERVAL="NONE"   /* All tables in one sheet*/
            SHEET_NAME ="LEVELS"
            EMBEDDED_TITLES="YES");

PROC ARIMA data= abc;
    by grp;
 TITLE "ARIMA IDENTIFY - U in levels";

    identify var=  testvar
    stationarity = (adf=(3));
    ods html (id=&pt.)  select StationarityTests SeriesCorrPanel;
    ods EXCEL (id=&pt.) select StationarityTests ;
RUN;

/************************************
   If I include the CLOSE here 
    then the ODS files declared above are empty

*********************************/
ODS _ALL_ CLOSE;


/*  =========================================== */
/*          PROC ARIMA for Stationarity
                    Differences                 */
/*  =========================================== */
%let pt    = 2;
%let ARpt2 = YonY_diff;

ODS graphics on / reset= INDEX imagename="&ARpt2.";

filename X_ADF&pt. "&outDir.&ARIMAdir.ARIMA_ADF_&ARpt2..xlsx" ;
filename H_ADF&pt. "&outDir.&ARIMAdir.ARIMA_ADF_&ARpt2..html" ;
filename g_ADF&pt. "&outDir.&ARIMAdir." ;

ods html  (id=&pt.) file = H_ADF&pt.   gpath = g_ADF&pt.;
ods EXCEL (id=&pt.) file = X_ADF&pt.  
        options(SHEET_INTERVAL="NONE"    /* All tables in one sheet*/
                SHEET_NAME ="Lvl Diff"
                EMBEDDED_TITLES="YES");/* Title Put in EXCEL doc */ 

PROC ARIMA data= abc;
    by grp;
    TITLE "ARIMA IDENTIFY - First Differences by City";

    identify var= testvar(1)    stationarity = (adf=(3));
    ods html  (id=&pt.)         select StationarityTests SeriesCorrPanel;
    ods EXCEL (id=&pt.)         select StationarityTests ;
 RUN;

/************************************
   If I include the CLOSE here 
    then the ODS files (html and excel) declared here
   are filled with the data from the FIRST proc arima 
*********************************/
ODS _ALL_ CLOSE;

Can anyone tell if its a problem with my code, or a particular issue with ARIMA?

**** DATA SET abc CREATED HERE ****

data a;
 u1 = 0.9; a1 = 0;
 do i = -50 to 100;
    a = rannor( 32565 );
    u = u1 + a - .8 * a1;
    if i > 0 then output;
    a1 = a;
    u1 = u;
 grp = "a";
end;
run;
data b;
 u1 = -0.5; a1 = 0;
    do i = -50 to 100;
       a = rannor( 876196 );
       u = u1 + a - .8 * a1;
       if i > 0 then output;
       a1 = a;
       u1 = u;
     grp = "b";
    end;
run;
data c;
     u1 = 5; a1 = 0.1;
     do i = -50 to 100;
        a = rannor( 876196 );
        u = u1 + a - .8 * a1;
        if i > 0 then output;
        a1 = a;
        u1 = u;
     grp = "c";
    end;
run;
data abc;
   merge a b c;
by grp;
run;

I wonder if you have a problem with the fact that `PROC ARIMA` is interactive (run-group) and expects a `QUIT;`, though I don't see an obvious connection (RUN should cause the output object to be created, and in a quick test it seems like it behaves how I expect it and how your program expects it). I would recommend creating a simpler example - one that uses simulatable data so we can see it on our side - and replicate the issue that way. — Joe, Aug 15 '16 at 16:25
You might use http://support.sas.com/documentation/cdl/en/etsug/60372/HTML/default/viewer.htm#etsug_arima_sect055.htm as your base example, as it's readily available for us to use. That's what I used in my example test, which produced output like you would expect. — Joe, Aug 15 '16 at 16:26
Thanks @Joe - that helped me isolate the issue to the `BY` statement. The problem only seems to arise when I'm doing `ARIMA` by group. I've updated the question in this regard and included some reproducible data adapted from that example test. — Tony Beans, Aug 15 '16 at 18:23

score 1 · Accepted Answer · answered Aug 15 '16 at 18:36

The problem is indeed the position of QUIT; when by-group processing is used. That is because the RUN; doesn't do anything when you are using by-group processing (as SAS would prefer to know all of the things you want to do for each BY-group prior to doing any of them). So your ODS HTML CLOSE; and such don't execute when you think they do.

Here's a token example that shows this problem. Try running without the comment on QUIT; and then with it, see the difference.

   title1 'Simulated IMA(1,1) Series';
   data a;
     dummy=1;
     u1 = 0.9; a1 = 0;
     do i = -50 to 100;
        a = rannor( 32565 );
        u= u1 + a - .8 * a1;
        if i > 0 then output;
        a1 = a;
        u1 = u;
     end;
   run; 


   ods html3 file="c:\temp\test.html";
   ods excel file="c:\temp\test.xlsx";
   proc arima data=a;
     by dummy;
     identify var=u;
     ods html3 select DescStats;
     ods excel select  SeriesCorrPanel;
     run;
     *quit;
  ods html3 close;
  ods excel close;

  *Here I put them out to a different file to make more obvious what is happening;
  ods html3 file="c:\temp\test1.html";
  ods excel file="c:\temp\test1.xlsx";
  proc arima data=a;
    by dummy;
     ods html3 select DescStats;
     ods excel select  SeriesCorrPanel;
     identify var=u(1);
     run;
    *quit;
  ods html3 close;
  ods excel close;
quit;

Ah I see. Using `quit;` indeed solves the problem. Trying to undertand this better, I came across your excellent answer here: http://stackoverflow.com/questions/33764328/quit-vs-run-statements-in-sas, which is useful in general and for this issue in particular. Thanks again! — Tony Beans, Aug 15 '16 at 19:55

SAS ODS Sandwich in PROC ARIMA with BY statement generate strange results

1 Answers1