Update Excel output using ODS?

Question

I am running a macro program to analyze a data set. In the end of my macro, i used a ODS statement and a proc Report to export my results. What i want is: For each time i run the macro program with a new data set, the results will be updated in a new sheet in the same Excel file, without delete the sheets of the old data sets, nor the old Excel file. Please help me.

Answer 1

The basic way to approach this is to have the main ods tagsets.excelxp statement outside of the macro iterations. Then only control the sheet inside the macro.

Say you have:

%macro run_me(sheet=,sex=);
  ods tagsets.excelxp options(sheet_name="&sheet.");
  proc print data=sashelp.class;
    where sex="&sex.";
  run;
%mend run_me;

ods tagsets.excelxp file="c:\temp\test.xml";
  %run_me(sheet=Male,sex=M);
  %run_me(sheet=Female,sex=F);
ods tagsets.excelxp close;

You just have to be careful with your output in the rest of the analysis; you may need to use ods select statements like so:

ods tagsets.excelxp select none;

at the start of the macro, and then when you want to start outputting again

ods tagsets.excelxp select all;

(Or just select the specific output you want, of course.)