The SAS Output Delivery System and reporting techniques

formats and style lost in tagsets.excelxp

Reply
New Contributor
Posts: 2

formats and style lost in tagsets.excelxp

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;
SAS Super FREQ
Posts: 8,743

Re: formats and style lost in tagsets.excelxp

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
New Contributor
Posts: 2

Re: formats and style lost in tagsets.excelxp

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.
SAS Super FREQ
Posts: 8,743

Re: formats and style lost in tagsets.excelxp

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
N/A
Posts: 0

Re: formats and style lost in tagsets.excelxp

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
SAS Super FREQ
Posts: 8,743

Re: formats and style lost in tagsets.excelxp

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
Ask a Question
Discussion stats
  • 5 replies
  • 256 views
  • 0 likes
  • 3 in conversation