The SAS Output Delivery System and reporting techniques

Creating a multiple sheets Excel output with ODS

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

Creating a multiple sheets Excel output with ODS

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. Smiley Sad

 

Any ideas? Thank you in advance.


Accepted Solutions
Solution
‎12-16-2015 11:53 AM
Grand Advisor
Posts: 16,342

Re: Creating a multiple sheets Excel output with ODS

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

View solution in original post


All Replies
Solution
‎12-16-2015 11:53 AM
Grand Advisor
Posts: 16,342

Re: Creating a multiple sheets Excel output with ODS

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
Contributor
Posts: 27

Re: Creating a multiple sheets Excel output with ODS

Thanks a lot!
Contributor
Posts: 27

Re: Creating a multiple sheets Excel output with ODS

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

Grand Advisor
Posts: 16,342

Re: Creating a multiple sheets Excel output with ODS

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.
Contributor
Posts: 27

Re: Creating a multiple sheets Excel output with ODS

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.

Grand Advisor
Posts: 16,342

Re: Creating a multiple sheets Excel output with ODS

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;

 

Contributor
Posts: 27

Re: Creating a multiple sheets Excel output with ODS

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. Smiley Sad What does sheet_interval = "page" do? For some reason I cannot google a clear explanation on that....Thank you a lot. 

Grand Advisor
Posts: 16,342

Re: Creating a multiple sheets Excel output with ODS

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;
Contributor
Posts: 27

Re: Creating a multiple sheets Excel output with ODS

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. 

Grand Advisor
Posts: 16,342

Re: Creating a multiple sheets Excel output with ODS

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 Smiley Sad
Frequent Contributor
Posts: 84

Re: Creating a multiple sheets Excel output with ODS

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');

Post a Question
Discussion Stats
  • 11 replies
  • 1016 views
  • 0 likes
  • 3 in conversation