I am sorry if this is a repetition, but I cannot find a solution to my problem. I would like to create an Excel output file with multiple sheets. For some reason nothing works for me.
I try :
ODS EXCEL file="output.xlsx"
style=HTMLBlue
options(
embedded_titles='yes'
sheet_interval="none"
sheet_name="Sheet 1"
);
%someMacroFunction_ThatPrints_TheOutput;
ODS excel options(sheet_name="Sheet 2");
%anotherMacroFunction_ThatPrints_TheOutput;
ODS Excel close;
What I get is a file with a single sheet "Sheet 1". I tried to replace ODS EXCEL with ODS tagsets.excelxp . Same results. 😞
Any ideas? Thank you in advance.
Thank you again. May I ask a bit more on this.
I changed the code to
ODS EXCEL file="output.xlsx"
style=HTMLBlue
options(
embedded_titles='yes'
sheet_interval="PROC"
sheet_name="Sheet 1"
);
%someMacroFunction_ThatPrints_TheOutput;
ODS excel options(sheet_name="Sheet 2");
%anotherMacroFunction_ThatPrints_TheOutput;
ODS Excel close;
But now ever single PROC is printed in a separate sheet. I tried something like
ODS excel options(sheet_name="Sheet 1" sheet_interval="none" );
%firstMacroFunction_ThatPrints_TheOutput;
ODS excel options(sheet_name="Sheet 2" sheet_interval="PROC");
%anotherMacroFunction_ThatPrints_TheOutput;
I get all PROCS in the firstMacroFunction_ThatPrints_TheOutput printed on a single sheet (as requested). But the rest are all printed on separate sheets. If I change the 3rd line to sheet_interval = 'none' I get everything on the single sheet (first macro AND the second macro).
I would appreciate your help.
Julia
I am sorry for not being clear. The macro functions just print a list of different freq tables. Stating simply I would like to have several tables on Sheet 1 and several more on Sheet 2.
ODS excel options(sheet_name="Sheet 1" sheet_interval="none" );
PROC FREQ .....
RUN;
PROC FREQ .....
RUN;
PROC FREQ .....
RUN;
ODS excel options(sheet_name="Sheet 2" sheet_interval="PROC");
PROC FREQ ...
RUN;
PROC FREQ...
RUN;
What I get by this code is the first 3 FREQ results on a single sheet (as expected) and the last two each one on its own sheet. I would like to have three first FREQ results on Sheet 1 and the last two PROC FREQ results on Sheet 2. Thank you.
Untested, but I think you'd have to switch it after you switched it to proc and then back to none.
ODS excel options(sheet_name="Sheet 2" sheet_interval="PROC");
PROC FREQ ...
RUN;
ODS excel options(sheet_name="Sheet 2" sheet_interval="NONE");
PROC FREQ...
RUN;
Yeah.. This would work if I wanted a single PROC printed on Sheet 1 and then multiple PROCs printed on Sheet 2. However, when I want multiple PROCs printed on Sheet 1 and then again multiple PROCs printed on Sheet 2 it doesn't work. 😞 What does sheet_interval = "page" do? For some reason I cannot google a clear explanation on that....Thank you a lot.
Ok..apparently you declare the option with sheet_interval='none' to create a new page. Everytime you want a new page, set the option again. Also, make sure you're using the latest version of tagsets from t he webpage.
http://support.sas.com/rnd/base/ods/odsmarkup/index.html
ods tagsets.excelxp file='C:/_localdata/sample.xml' options(sheet_interval='none');
proc print data=sashelp.class;
run;
proc print data=sashelp.class;
run;
ods tagsets.excelxp options(sheet_interval='none');
proc print data= sashelp.air;
run;
proc print data=sashelp.cars;
run;
ods tagsets.excelxp close;
Thank you... I am actually using
ODS Excel (rather than ODS tagsets.excelxp because it's important for me to create an output as an Excel file (not as xml). Do you think it is a problem? I also assumed that calling sheet_interval = 'none' should create a new sheet every time I call it. Apparentely, it doesn't.
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 25. 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.