The SAS Output Delivery System and reporting techniques

ODS excel & multiple sheets

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

ODS excel & multiple sheets

[ Edited ]

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


Accepted Solutions
Solution
‎04-06-2016 11:14 PM
Super User
Posts: 19,770

Re: ODS excel & multiple sheets

So it looks like a known bug Smiley Sad

 

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;

View solution in original post


All Replies
Regular Contributor
Posts: 216

Re: ODS excel & multiple sheets

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

Super User
Posts: 19,770

Re: ODS excel & multiple sheets

ODS Excel is still buggy Smiley Sad

 

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. 

 

 

Solution
‎04-06-2016 11:14 PM
Super User
Posts: 19,770

Re: ODS excel & multiple sheets

So it looks like a known bug Smiley Sad

 

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;
Occasional Contributor
Posts: 6

Re: ODS excel & multiple sheets

Thank you Reeza, that dummy table works well!!!
Super User
Posts: 3,250

Re: ODS excel & multiple sheets

The EXCELXP tagset works reliably for multiple sheets.

Super User
Posts: 19,770

Re: ODS excel & multiple sheets

Tagsets doesn't support graphics, ODS excel does. 

New Contributor
Posts: 3

Re: ODS excel & multiple sheets

[ Edited ]

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

Super User
Posts: 10,018

Re: ODS excel & multiple sheets

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;

New Contributor
Posts: 3

Re: ODS excel & multiple sheets

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.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 4658 views
  • 10 likes
  • 6 in conversation