ExcelXp, Proc tabulate & template

[AliciaB]

Good morning,

First off, let me say that I am VERY new to proc template and excelxp. I am using tagsets.excelxp along with a basic template for my tabulate procedure. The one aspect I can't figure out formatting-wise is the header in Excel. The header continues to print as Calibri 11 pt when I want Tahoma 8 pt.

Here is my template procedure:

proc template;
define style Styles.ispatahoma8;
parent = Styles.printer;
STYLE SystemTitle /
FONT_FACE = "Tahoma"
FONT_SIZE = 11pt
FONT_WEIGHT = bold
FOREGROUND = black
BACKGROUND = white;
STYLE SystemFooter /
FONT_FACE = "Tahoma"
FONT_SIZE = 8pt
JUST = L
FOREGROUND = black
BACKGROUND = white;
STYLE Header /
FONT_FACE = "Tahoma"
FONT_SIZE = 8pt
JUST = R
FOREGROUND = black
BACKGROUND = white
BORDERTOPCOLOR = green
BORDERTOPWIDTH = 3;
STYLE RowHeader /
FONT_FACE = "Tahoma"
FONT_SIZE = 8pt
FOREGROUND = black
BACKGROUND = white;
STYLE Data /
FONT_FACE = "Tahoma"
FONT_SIZE = 8pt
FOREGROUND = black
BACKGROUND = white;
STYLE Table /
FOREGROUND = black
BACKGROUND = white
CELLSPACING = 0
CELLPADDING = 3;
STYLE Body /
FOREGROUND = black
BACKGROUND = white;
end;
run;


Here are the macros & excelxp code:

%let dir= C:\Documents and Settings\abetsinger\My Documents\My SAS Files;
%let fname1=enrolltest v2.xls;
%let term=FALL;
%let year=2009;
%let header =
&LThe University of Texas System
&#13Institutional Studies and Policy Analysis
&RFacts and Trends, 2009-2010;

options
leftmargin = .76in
rightmargin = .66in
topmargin = .87in
bottommargin = .5in;


ods listing close;
ods tagsets.excelxp
file="&dir\&fname1"
style=ispatahoma8
options (embedded_titles='yes'
embedded_footnotes='yes'
print_header="&header"
absolute_column_width="13.71,12.71,11.86,11.86,11.86,11.86,11.86"
orientation='portrait'
sheet_label= ' '
suppress_bylines= 'yes'
row_heights='11,10.5,0,14.25,9'
width_fudge='0.7'
PRINT_FOOTER_MARGIN='0.41');

Any and all advice is greatly appreciated!
Alicia

[Cynthia_sas]

Hi:
The HEADER style element in the PROC TEMPLATE code -only- has an impact on the header cells built by PROC TABULATE (or by any SAS procedure). So when you have a CLASS or VAR statement in the TABULATE code, the HEADERS and levels of the CLASS variables should all be impacted by the HEADER style element or the ROWHEADER style element (for the class variable levels in the row dimension).

It was my understanding that the "internal to Excel" Print Header (note that the ExcelXP option is "print_header") -- was controlled by Excel, not by ODS. You can control the content of the print header string, but I thought that Excel defaults controlled the font, size etc of the print header.

For example, when you specify this:

%let header =&LThe University of Texas System&#13Institutional Studies and Policy Analysis&RFacts and Trends, 2009-2010;


...the &L and &R are internal-to-Excel commands that determine justification -- they "belong" to Excel. The style template HEADER element belongs to the procedure output -- to the tabular report output -- what you see on the main worksheet page. The PRINT_HEADER option is being passed to Excel so it can be used in Excel's Print Preview mode -- therefore, I believe that only Excel methods can be used to format the PRINT_HEADER and PRINT_FOOTER.

When TAGSETS.EXCELXP writes the PRINT_HEADER instruction, it creates a "PageSetup" section in the XML. Tech Support would be your best resource to tell you whether this PageSetup can be impacted with a style template or whether you have to use Excel methods to format the text string.

cynthia