Creating a multiple sheets Excel output with ODS

[braverju]

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.

[Reeza · Accepted solution]

Sheet_Interval should be PROC.
None indicates that it all goes to one sheet.

SHEET_INTERVAL Options: Interval to divide the output between worksheets. Values are Table, Page, Bygroup, Proc, or None

[braverju]

Thanks a lot!

[braverju]

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

[Reeza]

I don't understand your question. You can control the output type by changing ods excel options in the middle but I have no idea what your functions are doing so I can't help with that. The option stays valid until you change it.

[braverju]

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.

[Reeza]

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;

[braverju]

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. 

[Reeza]

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;

[braverju]

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. 

[Reeza]

It can definitely be an issue with ODS Excel. I thought ODS Excel was still pre-production, didn't realize it was fully implemented yet. I'm on SAS 9.3 😞

[wcp_fnfg]

I'm really late to reply here, but you can customize the sheet breaks by throwing another sheet interval = 'none' wherever you want.

ods tagsets.excelxp options(sheet_interval="none" sheet_name = 'XX');

code

ods tagsets.excelxp options(sheet_interval="none" sheet_name = 'ZZ');