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