3

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.

Joe
  • 62,789
  • 6
  • 49
  • 67
buiquanghai
  • 81
  • 1
  • 6
  • of possible interest: http://support.sas.com/resources/papers/proceedings13/143-2013.pdf – C8H10N4O2 Jul 01 '15 at 12:50
  • @C8H10N4O2 Thank you but i think it works only if i run all my ODS statement in the same macro , before i close the main ODS. What i want is to update a Excel file after closing the macro program and the ODS statement. – buiquanghai Jul 01 '15 at 13:51
  • @C8H10N4O2 In the page 2 of the doc, it said that we cannot use the techniques described in the paper to update the existing workbook. – buiquanghai Jul 01 '15 at 13:53
  • Right - hence my comment, not an answer. You might be forced to read in all sheets, store the old sheets in .work, then re-export those old sheets along with the new sheets and overwrite the Excel file. Thus effectively adding a new sheet. – C8H10N4O2 Jul 01 '15 at 14:10
  • @C8H10N4O2 Can you tell me which proc or statement should i use in order to do that please? – buiquanghai Jul 01 '15 at 14:15

1 Answers1

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.)

Joe
  • 62,789
  • 6
  • 49
  • 67
  • Thank you Joe. I think we are close. But i'm wonder if we can make the code more automatic? For example: In the macro run_me, i will remove the Where Statement, and add a macro variable for the data_input: run_me(sheet= , data_input=). Then in the next ODS tagsets statement, i don't want to repeat manually the %run_me for all my data sets. So If i have a library that contains all my data sets, does SAS have a function that will analyze automatically all the data sets in that library, one by one? – buiquanghai Jul 02 '15 at 07:27
  • For example, if we can count the number of data sets in a library, then call each data set in that library by its order (1 2 3 4...), that will allow us to do a loop. – buiquanghai Jul 02 '15 at 07:35
  • Ok i found it, i test the code then i will update the question if i found the answer. – buiquanghai Jul 02 '15 at 08:15
  • You can certainly do what you say; there's not a built in SAS function to do that, but you can generate macro calls from a dataset, or from SAS's metadata. – Joe Jul 02 '15 at 14:38
  • For example, [this answer](http://stackoverflow.com/questions/22722069/loop-over-a-list-of-variables-inside-a-macro-read-one-each-time/22722907#22722907) shows one way to do so. – Joe Jul 02 '15 at 14:38