topic Re: Creating a multiple sheets Excel output with ODS in ODS and Base Reporting

Creating a multiple sheets Excel output with ODS

braverju — Tue, 15 Dec 2015 20:23:55 GMT

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.

Re: Creating a multiple sheets Excel output with ODS

Reeza — Tue, 15 Dec 2015 20:28:11 GMT

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

Re: Creating a multiple sheets Excel output with ODS

braverju — Wed, 16 Dec 2015 16:53:48 GMT

Thanks a lot!

Re: Creating a multiple sheets Excel output with ODS

braverju — Wed, 16 Dec 2015 20:14:37 GMT

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

Re: Creating a multiple sheets Excel output with ODS

Reeza — Wed, 16 Dec 2015 20:23:12 GMT

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.

Re: Creating a multiple sheets Excel output with ODS

braverju — Wed, 16 Dec 2015 20:29:59 GMT

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.

Re: Creating a multiple sheets Excel output with ODS

Reeza — Wed, 16 Dec 2015 22:31:38 GMT

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;

Re: Creating a multiple sheets Excel output with ODS

braverju — Thu, 17 Dec 2015 13:59:45 GMT

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.

Re: Creating a multiple sheets Excel output with ODS

Reeza — Thu, 17 Dec 2015 15:31:44 GMT

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;

Re: Creating a multiple sheets Excel output with ODS

braverju — Mon, 21 Dec 2015 20:30:36 GMT

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.

Re: Creating a multiple sheets Excel output with ODS

Reeza — Mon, 21 Dec 2015 20:35:08 GMT

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 😞

Re: Creating a multiple sheets Excel output with ODS

wcp_fnfg — Thu, 06 Oct 2016 20:17:00 GMT

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