ODS output in loop SAS

Question

I am trying to output the compare result to excel or pdf for all the output from the library. here is what I wrote

%DO  I = 1 %TO &coun; 
%LET DT = %SCAN (&DS_LIST., &I., ~); 

ods excel file="D:\test.xlsx";

PROC COMPARE BASE = BASE.&DT. COMPARE = COMPARE.&DT. OUTNOEQUAL OUTBASE
 OUTCOMP OUT = COMPAREOUT; 
RUN
;  
ods excel close;

however it gives me only the last dataset output.. what is wrong here?

Richard Richard · Accepted Answer · 2018-05-30T15:18:16

You have not shown the %END for the %DO loop. I would presume you are opening and closing the ODS EXCEL destination inside the loop. ODS EXCEL does not append to an existing file, so you are only getting the comparison output from the last data set in the list. Try this

* open ODS destination;
ods excel file="D:\test.xlsx";

* loop over data sets in DS_LIST, get compare report for each;
%DO  I = 1 %TO &coun;
  %LET DT = %SCAN (&DS_LIST., &I., ~); 

  PROC COMPARE BASE = BASE.&DT. COMPARE = COMPARE.&DT. OUTNOEQUAL OUTBASE
   OUTCOMP OUT = COMPAREOUT; 
  RUN;  
%END;

* close the ODS destination that contains the output from &coun comparisons;
ods excel close;

ODS output in loop SAS

2 Answers