ods excel multiple sheets

[Sir_Highbury]

Dear experts,

I saw the following link:

http://blogs.sas.com/content/sasdummy/2014/08/29/experimenting-with-ods-excel-to-create-spreadsheets...

and I am trying to getting familiar with the ods excel that looks interesting (despite my sas version does not allow me to use the proc export with the format .xlsx).

Where should I change this code to get the sme data also in another worksheet?

ods excel file="W:\03-OUT_outputfolder\example.xlsx"
/* will apply an appearance style */
style=pearl
options(
/* for multiple procs/sheet */
sheet_interval="none"
/* name the sheet tab */
sheet_name="Example"
);
/* add some formatted text */
ods escapechar='~';
ods text="~S={font_size=14pt font_weight=bold}~Abc def ghi";
/* tabular output */
proc print data=perm.Summary_table_xxxxxxxx;
var _all_;
title 'table';
footnote '*footnote';
run;
ods excel close;

Thanks, SH.

[Tim_SAS]

The creation of new worksheets is controlled by the SHEET_INTERVAL keyword in the OPTIONS() option. Your program sets sheet_interval="none", so all the program output will go on the same worksheet. Change the value of sheet_interval as necessary to put your program output to go on new sheets.

[Sir_Highbury]

Hi Tim,

ok, I was also guessing that if sheet_interval="none" I would not get several sheets and this parameter should be edited. My question is how? For instance i changed the code as it follows:

ods excel file="W:\03-OUT_outputfolder\example.xlsx"
/* will apply an appearance style */
style=pearl
options(

/* for multiple procs/sheet
sheet_interval="none" */
sheet_interval="table"
/* name the sheet tab */
sheet_name="Example"
);

/* add some formatted text */
ods escapechar='~';
ods text="~S={font_size=14pt font_weight=bold}~Abc def ghi";

/* tabular output */
proc print data=table1;
var _all_;
title 'table';
footnote '*footnote';
run;

/* add some formatted text */
ods escapechar='~';
ods text="~S={font_size=14pt font_weight=bold}~tttttttttt";
proc print data=table2;
var _all_;
title 'table';
footnote '*footnote';
run;

ods excel close;

I get the tables on two separate sheets but:

- the second sheet is named Example2, where should I specify a different name?

- I added also the text (ods escapechar='~'; ods text="~S={font_size=14pt font_weight=bold}~tttttttttt";) but it is still on the first wook sheet, where can I set the break between the two pages?

If I am not wrong I was thinking to a similis logik like writing documents using Latex.

Thanks a gain and best regards, SH

[Reeza]

You can review the options for ODS Excel by looking at the documentation for ODS tagsets since they're so similar. 

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html

Sheet Options are here. 

http://support.sas.com/documentation/cdl/en/odsug/67921/HTML/default/viewer.htm#p09n5pw9ol0897n1qe04...

You're looking for sheet_label.

[Sir_Highbury]

Thanks! since it is about exporting and formats, is there not a library with some templates that can be easily copied, re-adjusted and used (like for latex)? 

[Reeza]

Not AFAIK, it's a good idea though. 

The first link has the base idea and you can find various papers on lexjansen.com. 

There are a lot of options so the number of permutations are large. 

[Sir_Highbury]

ok, anyway my issue is not yet fixed and I did not find any case with sheet_interval="table".

From my perspective a bunch of exemplary templates would be for me more helpful than merely teoretical descriptions.

[Reeza]

You don't have a SAS Access to PCFiles license? 

Proc setinit; run; 

[Sir_Highbury]

Dear Reeza,

attached the result. Am I wrong?

BRs, 

SH