BookmarkSubscribeRSS Feed
Sir_Highbury
Quartz | Level 8

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.

 

8 REPLIES 8
Tim_SAS
Barite | Level 11

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
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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
Super User

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
Quartz | Level 8

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
Super User

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

 

 

Proc setinit; run; 

Sir_Highbury
Quartz | Level 8

Dear Reeza,

 

attached the result. Am I wrong?

 

BRs, 

SH


sas.JPG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1306 views
  • 0 likes
  • 3 in conversation