DATA Step, Macro, Functions and more

ods excel multiple sheets

Reply
Frequent Contributor
Posts: 127

ods excel multiple sheets

[ Edited ]

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.

 

Super Contributor
Posts: 394

Re: ods excel multiple sheets

Posted in reply to Sir_Highbury

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.

Frequent Contributor
Posts: 127

Re: ods excel multiple sheets

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"Smiley Wink 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

Super User
Posts: 19,855

Re: ods excel multiple sheets

Posted in reply to Sir_Highbury

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.

Frequent Contributor
Posts: 127

Re: ods excel multiple sheets

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

Super User
Posts: 19,855

Re: ods excel multiple sheets

Posted in reply to Sir_Highbury

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. 

Frequent Contributor
Posts: 127

Re: ods excel multiple sheets

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.

Super User
Posts: 19,855

Re: ods excel multiple sheets

Posted in reply to Sir_Highbury

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

 

 

Proc setinit; run; 

Frequent Contributor
Posts: 127

Re: ods excel multiple sheets

[ Edited ]

Dear Reeza,

 

attached the result. Am I wrong?

 

BRs, 

SH


sas.JPG
Ask a Question
Discussion stats
  • 8 replies
  • 381 views
  • 0 likes
  • 3 in conversation