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!!
So it looks like a known bug 😞
Here's the solution, via Andrea Zimmerman via the comments here:
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;
Hi,
According to this blog
"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
ODS Excel is still buggy 😞
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.
So it looks like a known bug 😞
Here's the solution, via Andrea Zimmerman via the comments here:
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;
The EXCELXP tagset works reliably for multiple sheets.
Tagsets doesn't support graphics, ODS excel does.
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!!
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: