topic Re: ODS excel multiple sheet in ODS and Base Reporting

ODS excel multiple sheet

proc_sortt — Mon, 14 Sep 2015 14:27:45 GMT

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,

Re: ODS excel multiple sheet

RW9 — Mon, 14 Sep 2015 14:33:11 GMT

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;

Re: ODS excel multiple sheet

proc_sortt — Mon, 14 Sep 2015 15:09:12 GMT

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

🙂

Re: ODS excel multiple sheet

Cynthia_sas — Mon, 14 Sep 2015 15:28:39 GMT

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

Re: ODS excel multiple sheet

proc_sortt — Mon, 14 Sep 2015 16:56:22 GMT

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 !

Re: ODS excel multiple sheet

krritter — Fri, 04 Dec 2015 14:04:18 GMT

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