ODS excel multiple sheet

[proc_sortt]

Dear all,

I am writing an excel report by using the new experimental ods dsestination: EXCEL.

I simply would like to know if it is possible to write into several sheet, then same way excel tagset does: by calling ods excel with a different sheet_name option:

ods excel (id=sheet1) file=xlsheet options(sheet_interval="none" sheet_name="summary1");

PROC REPORT 1

....

ods excel (id=sheet2) options(sheet_name="summary2") ;

PROC REPORT 2

....

ods _all_ close ;

So far I only manage to get the last report to be displayed and I don't want to use PROC EXPORT.

Thank you for your help,

[RW9 · Accepted solution]

This works fine for me:

ods excel file="s:\temp\rob\temp.xlsx" options(sheet_name="sheet1");
proc print data=sashelp.cars;
run;
ods excel options(sheet_name="sheet2");
proc print data=sashelp.class;
run;
ods excel options(sheet_name="sheet3");
proc print data=sashelp.cars;
run;
ods excel close;

[proc_sortt]

Thank you very much for the solution, I just removed the id statement and then it worked smoothly !

🙂

[Cynthia_sas]

Hi:

  ID= is meant to be used when you are opening multiple instances of the SAME destination, which is not what you needed to do. That's why it did not work in your case. A good example of using ID= with ODS Excel and ID= is shown below:

ods excel(id=one) file='c:\temp\out1_sasweb.xlsx' style=sasweb
    options(sheet_name='first_in_one');
ods excel(id=two) file='c:\temp\out2_htmlblue.xlsx' style=htmlblue
    options(sheet_name='first_in_two');

proc print data=sashelp.class;
run;

ods excel(id=one) options(sheet_name='xxx');
ods excel(id=two) options(sheet_name='yyy');

proc print data=sashelp.shoes(obs=3);
run;

ods _all_ close;

  So, in this example code, 2 separate files (with different names) are created from the same procedures using the SAME destination, ODS EXCEL. Each file used a different style and each file has different names for the tabs. So the sheet names for the out1_sasweb.xlsx file will be first_in_one and xxx and the sheet names for the out2_htmlblue.xlsx will be first_in_two and yyy.

cynthia

[proc_sortt]

Thank you Cynthia, this is a very clear example!

Back to my problem, 

I am using ods text to push text into the excel report, but something strange happend, the text is going into the first tab, which is not what I want. Do you have maybe an idea why ?

ods excel options(
sheet_name="Prog summary"
);
/* Datasets detailed report */
ods text="~S={font_size=14pt font_weight=bold}~ blop3" ;
ods text="~S={font_size=14pt font_weight=bold}~ blop4" ;
ods text="~S={font_size=14pt font_weight=bold}~ Summary of datasets" ;

Thank you !

[krritter]

Did you ever get a solution to the problem of ods text titles?