BookmarkSubscribeRSS Feed
Shirley
Calcite | Level 5
I hope any one can help me on this.


I formatted my report and exported to Excel using the following code. I get two excel files with the format and style I wanted.


ods html file="="C:\report1.xls" style=minimal;
ods noptitle;

proc report data=report1 …..;

SAS code ….


ods html file="C:\report2.xls" style=minimal;
ods noptitle;
proc report data=report2 ….;

SAS code ….

title1 font=calibri bold "CUSTOMER DATA";
title2 font=calibri bold “FY2009" ;
title3 font=calibri bold “Region2”;
run ;

ods html close;


However, I want to put “report1” and “report2” into the same excel file with two tabs. So I used the tagsets.excelxp as show. However, I lost the most all my styles and formats from code above.

ODS tagsets.excelxp file = ="C:\report.xls"

proc report data=report1 …..;

SAS code ….


proc report data=report2 ….;

SAS code ….

title1 font=calibri bold "CUSTOMER DATA";
title2 font=calibri bold “FY2009" ;
title3 font=calibri bold “Region2”;
run ;


ods tagsets.excelxp close;
5 REPLIES 5
Cynthia_sas
SAS Super FREQ
Hi:
This worked for me in SAS 9.2 with the latest ExcelXP tagset:
[pre]
ods tagsets.excelxp file="C:\temp\xpreportzz.xls" style=minimal
options(Doc='Help' embedded_titles='yes' sheet_name='Report1');
ods noptitle;
title f='Arial' h=14pt 'Title1';
proc report data=sashelp.class nowd;
run;

ods tagsets.excelxp options(sheet_name='Report2' embedded_titles='yes');

title1 f='Calibri' h=14pt bold "CUSTOMER DATA";
title2 f='Calibri' h=12pt bold "FY2009" ;
title3 f='Calibri' h=11pt bold "Region2";
proc report data=sashelp.class nowd;
run;

ods tagsets.excelxp close;
[/pre]

By default, titles are put into the Header area of the Workbook/Worksheet. So you never see your SAS title unless you are in Print Preview mode. However, the embedded_titles suboption allows you to embed your titles into the spreadsheet. The sheet_name suboption allows you to name each sheet. You do not need to respecify style=minimal between sheets.

I also used the DOC='Help' suboption -- it is the self-documenting help file that displays all the ExcelXP suboptions in the SAS Log -- very helpful.

I used the H= attribute in the title statements to make sure that my fonts and sizes were getting set (and they are). I am also in the habit of quoting fonts and using the -exact- name. On my system, the fonts are Calibri and Arial so those are the names I used in quotes.

Also, ODS NOPTITLE stays in effect until you start a new session or turn PTITLE back on. So you only need to issue that statement once. And, I assume that this syntax snippet from your post was just a typo -- you have too many = and too many " and/or a missing semicolon:
[pre]
ods html file="="C:\report1.xls" style=minimal; <--extra " extra =
ODS tagsets.excelxp file = ="C:\report.xls" <-- no semi, 2 =
[/pre]


cynthia
Shirley
Calcite | Level 5
Thanks Cynthia. I like your code.

We use SAS 9.1.3. I ran your code and exported tables into excel. Now, I can't open Excel worksheet. I wonder if this is related to SAS version we have been used.
Cynthia_sas
SAS Super FREQ
Hi:
Probably -not- the SAS version, but the ExcelXP version. You can update the tagset template used by ODS. The version that was delivered with SAS 9.1.3 did not do embedded_titles, if I remember correctly. I think that was an added enhancement. You can look in your SAS log to see which tagset template is being used.

This is the tagset version that I was using: [pre]
NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.94, 09/09/12) [/pre]


You can download updated tagset templates for ExcelXP from this site:
http://support.sas.com/rnd/base/ods/odsmarkup/index.html

This Tech Support note has the instructions for updating a tagset template: http://support.sas.com/kb/32/394.html

cynthia
deleted_user
Not applicable
Hi Cynthia,

The ExcelXP tagset available from the web page you refer to in your post is version 1.86, 04/15/08.

Where can I download version 1.94?


Kind regards
Bo Hovgaard Thomasen
Cynthia_sas
SAS Super FREQ
Hi:
I am using SAS 9.2 Phase 2 and v1.94 is the tagset template that's included with that release of SAS. I figure v1.94 will be available on the download web site when it's deemed OK for SAS 9.1.3. My guess (and it's only a guess) is that there -might- be testing involved before the tagset is posted to make sure that it does work with SAS 9.1.3.

cynthia

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
  • 5 replies
  • 922 views
  • 0 likes
  • 3 in conversation