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
You might use support.sas.com/documentation/cdl/en/etsug/60372/HTML/default/… 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
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

Joe Joe · Accepted Answer · 2016-08-15T18:36:14

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;

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

1 Answers