BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lfang
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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;

View solution in original post

9 REPLIES 9
AhmedAl_Attar
Rhodochrosite | Level 12

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
Super User

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
Super User

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
Fluorite | Level 6
Thank you Reeza, that dummy table works well!!!
SASKiwi
PROC Star

The EXCELXP tagset works reliably for multiple sheets.

Reeza
Super User

Tagsets doesn't support graphics, ODS excel does. 

Schoolmaster
Fluorite | Level 6

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
Super User
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
Fluorite | Level 6
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 36142 views
  • 20 likes
  • 6 in conversation