topic Re: ods excel multiple sheets in SAS Programming

ods excel multiple sheets

Sir_Highbury — Mon, 11 Jul 2016 12:52:43 GMT

Dear experts,

I saw the following link:

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

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.

Re: ods excel multiple sheets

Tim_SAS — Fri, 08 Jul 2016 14:51:47 GMT

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.

Re: ods excel multiple sheets

Reeza — Fri, 08 Jul 2016 21:48:40 GMT

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

Proc setinit; run;

Re: ods excel multiple sheets

Sir_Highbury — Mon, 11 Jul 2016 12:59:41 GMT

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

Re: ods excel multiple sheets

Sir_Highbury — Mon, 11 Jul 2016 13:15:57 GMT

Dear Reeza,

attached the result. Am I wrong?

BRs,

Re: ods excel multiple sheets

Reeza — Mon, 11 Jul 2016 14:21:39 GMT

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#p09n5pw9ol0897n1qe04zeur27rv.htm

You're looking for sheet_label.

Re: ods excel multiple sheets

Sir_Highbury — Mon, 11 Jul 2016 14:42:31 GMT

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)?

Re: ods excel multiple sheets

Reeza — Mon, 11 Jul 2016 14:57:19 GMT

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.

Re: ods excel multiple sheets

Sir_Highbury — Mon, 11 Jul 2016 15:11:14 GMT

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.