ODS excel & multiple sheets

[lfang]

Dear all,

I am trying to output to XLSX by using ODS EXCEL.  I want to output multiple tabs, and there are multiple proc in each tab.  here is my program:

ods excel file="c:\temp\temp.xlsx" options(sheet_interval="none" sheet_name="sheet 1");
ods graphics / height=400 width=800 noborder;
proc sgplot data=sashelp.cars;
 histogram msrp;
run;
proc print data=sashelp.class;
run;
ods excel options(sheet_interval="none" sheet_name="sheet 2");
proc sgplot data=sashelp.cars;
 histogram msrp;
run;
proc print data=sashelp.class;
run;
ods excel close;

Even I specify two different sheet names, but since I use sheet_interval="none", so all the proc were output to "sheet 1" only.

Is there any way to solve this issus?

Thanks!!

[AhmedAl_Attar]

Hi,

According to this blog

http://blogs.sas.com/content/sasdummy/2014/08/29/experimenting-with-ods-excel-to-create-spreadsheets...

"ODS EXCEL overwrites any existing Excel file that you target. That means that you aren't going to use this method to poke new values into an existing spreadsheet, or add sheets to an existing workbook. Compare that to PROC EXPORT DBMS=XLSX, which allows you to update an existing workbook by targeting just one sheet."

You may want to investigate an alternative approach.

Good luck,

Ahmed

[Reeza]

ODS Excel is still buggy 😞

https://communities.sas.com/t5/ODS-and-Base-Reporting/Creating-a-multiple-sheets-Excel-output-with-O...

Not very helpful...you may want to open a ticket with tech support. 

I'll also move it to ODS reporting so perhaps Cynthia or someone else can see if there's a fix. 

[Reeza · Accepted solution]

So it looks like a known bug 😞

Here's the solution, via Andrea Zimmerman via the comments here:

http://blogs.sas.com/content/sasdummy/2014/08/29/experimenting-with-ods-excel-to-create-spreadsheets...

Ok, got an answer from SAS tech support. It is an issue with SP3 that will hopefully be fixed with SP4. You can put everything on one tab, or one thing per tab, but if you want a couple things on one tab, and a couple things on the next tab, you have to trick SAS into creating the new tab.
ods excel file="C:\elever.xlsx";
ods excel options(sheet_name="SkoleElever" sheet_interval="none");
proc print data=sashelp.class;
run;
proc print data=sashelp.class;
run;
/* Add dummy table */
ods excel options(sheet_interval="table");
ods exclude all;
data _null_;
file print;
put _all_;
run;
ods select all;
ods excel options(sheet_interval="none");
proc tabulate data=sashelp.class;
class age sex;
table age, sex;
run;
proc print data=sashelp.class;
where age=12;
run;
ods EXCEL close;

[lfang]

Thank you Reeza, that dummy table works well!!!

[SASKiwi]

The EXCELXP tagset works reliably for multiple sheets.

[Reeza]

Tagsets doesn't support graphics, ODS excel does. 

[Schoolmaster]

This fix is better than another fix I saw earlier because you use the Table sheet interval.  But, Tech Support should take some time to put in a real fix for this.  Perhaps, put in another sheet_interval switch that basically does the dummy file call behind the scenes.  But hey, this fix is what I'm looking for right now, my clients won't care it's not exotic...... it works!!

[Ksharp]

very interesting. It looks like you need reset these OPTIONS before make sheet_interval='non' happen again.



ods excel file="/folders/myfolders/temp.xlsx" options(sheet_interval="none" sheet_name="sheet 1");
ods graphics / height=400 width=800 noborder;
proc sgplot data=sashelp.cars;
 histogram msrp;
run;
proc print data=sashelp.class;
run;

ods excel options(sheet_interval="proc" sheet_name="sheet 2");
ods select none;
proc print data=sashelp.class;run;
ods select all;

ods excel options(sheet_interval="none" sheet_name="sheet 2");
proc sgplot data=sashelp.cars;
 histogram msrp;
run;
proc print data=sashelp.class;
run;
ods excel close;

[Schoolmaster]

So that is how that select all thing works! Looks like it needs to be turned 'on' when you reset the default destination of the first page.