topic Re: ODS excel & multiple sheets in ODS and Base Reporting

ODS excel & multiple sheets

lfang — Thu, 07 Apr 2016 01:29:13 GMT

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!!

Re: ODS excel & multiple sheets

AhmedAl_Attar — Thu, 07 Apr 2016 00:46:06 GMT

Hi,

According to this blog

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

"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

Re: ODS excel & multiple sheets

Reeza — Thu, 07 Apr 2016 01:38:08 GMT

ODS Excel is still buggy 😞

https://communities.sas.com/t5/ODS-and-Base-Reporting/Creating-a-multiple-sheets-Excel-output-with-ODS/m-p/239419/highlight/true#M14880

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.

Re: ODS excel & multiple sheets

Reeza — Thu, 07 Apr 2016 01:41:49 GMT

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-from-sas/

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;

Re: ODS excel & multiple sheets

lfang — Thu, 07 Apr 2016 03:07:46 GMT

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

Re: ODS excel & multiple sheets

Ksharp — Thu, 07 Apr 2016 03:42:53 GMT

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;

Re: ODS excel & multiple sheets

SASKiwi — Thu, 07 Apr 2016 08:38:24 GMT

The EXCELXP tagset works reliably for multiple sheets.

Re: ODS excel & multiple sheets

Reeza — Thu, 07 Apr 2016 09:22:48 GMT

Tagsets doesn't support graphics, ODS excel does.

Re: ODS excel & multiple sheets

Schoolmaster — Fri, 10 Mar 2017 05:01:34 GMT

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.

Re: ODS excel & multiple sheets

Schoolmaster — Wed, 12 Apr 2017 03:27:22 GMT

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!!