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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.