Try this. The main difference is that, in the destination for Excel, you have to prefix a custom format definition with the word "format:". I also changed the column widths and made a few other changes but that was the big one.
options dlcreatedir;
*libname newdir "/sas/data/Risk_Mgmt/users/pattowe/temp";
libname newdir '.';
/*filename testdir '/sas/data/Risk_Mgmt/users/pattowe/temp';*/
/*data _null_;*/
/* rc=fdelete('testdir');*/
/* put rc=;*/
/* msg=sysmsg();*/
/* put msg=;*/
/*run;*/
option compress=yes validvarname=upcase nolabel;
%let tag_dol = tagattr='format:$#,##0.0;[Red]($#,##0.0);-';
%let tag_comma = tagattr='format:#,##0.0;[Red](#,##0.0);-';
%let tag_pct = tagattr='format:0.00%;[Red]-0.00%;-';
proc template;
define style styles.wesweb;
parent = styles.sasweb;
style rowheader from rowheader /
background = #FFFFFF
color = #000000
font_weight= bold;
class container /
font_size = 10pt;
end;
run;
*Embedded subtotal, Highlight Subtotals as Blue, Grand Total Row as Orange;
*TagAttr necessary to carry format to excel with ODS Tagsets.ExcelXp;
ods listing close;
ods html close;
title;
options missing = 0;
ods excel
file= "Tabulate_Test.xlsx"
style=wesweb
options (
sheet_interval="none"
absolute_column_width = '7,15,9,9,9,9,9,9,11'
embedded_titles='yes'
embedded_footnotes='yes'
orientation = 'portrait'
center_horizontal = 'yes'
pages_fitwidth = '1'
pages_fitheight = '1'
print_header= "SASHelp Average Car Prices"
print_footer='&RPage &P of &N'
/*autofit_height='yes'*/
row_heights='26'
sheet_name = 'Prices')
;
proc tabulate data = sashelp.cars missing;
class origin make type;
var msrp;
table
(origin=''*{s={&tag_dol.}}
* (make='' all='Total'*{s={font_weight=bold background=#B8CCE4 &tag_dol.}}) /*Blue Highlight, Bold*/
all='Grand Total'*{s={font_weight=bold background=#FABF8F &tag_dol.}}) /*Orange Highlight, Bold*/
, (type='' all='Grand Total')
* (msrp=''*mean='' *f=comma22.2 ) /*Comma format for SAS report, does not carry to Excel*/
/box = {label='Mean MSRP' style={vjust=bottom}} row=float;
run;
ods excel close;
... View more
