Updating Excel Output Using ODS?
I am running a macro to parse a dataset. At the end of my macro, I used an ODS statement and a proc report to export my results. What I want is: Every time I run a macro with a new dataset, the results will update on a new sheet in the same Excel file, without deleting the old dataset sheets or the old Excel file. Please help me.
source to share
The main way to approach this is to have a main statement ods tagsets.excelxp
outside of the macro iterations. Then only manipulate the sheet inside the macro.
Say what 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 result for the rest of the analysis; you may need to use operators ods select
like:
ods tagsets.excelxp select none;
at the beginning of the macro and then when you want to start the output again
ods tagsets.excelxp select all;
(Or just pick the result you want, of course.)
source to share