Jen:
There are many different pieces at work in your program. The zero as text issue IS a tagset issue and I understand the developers are working on an updated version of the ExcelXP tagset that fixes that issue. You will have to contact Tech Support about obtaining that tagset.
So, for your other questions about 1) getting rid of styles and 2) getting the comma in Excel, I'm going to answer the second question first (because the first question will take more code...)
[pre]
tables all*{s={tagattr='format:#,###,#00'}} ageyrs=' ',
all datayear=' ' / rts=20 misstext='0'; [/pre]
the TAGATTR style override is sending a MICROSOFT format to Excel. This style override is telling ODS to send commas as the thousands separator ('format:#,###,#00'). You don't need the 'format:' if you're just sending a format, but I thought it looked better to explicitly send 'format:' for completeness. You would have to use that same style attribute override for any table components that you thought would need the comma format.
How you minimize the ODS styles is by using a modified style template. The key to modifying the style template is the fact that you either specify a value for certain attributes, like background color, foreground color and borderwidth (to name a few attributes) OR you turn them off. Turning them off is accomplished by using the _UNDEF_ style attribute. It's like saying -- I don't care what attributes are used, just use the default attributes of the rendering software. So, in my code below, there's a PROC TEMPLATE step that modifies the Statistical style to remove just about all the style attributes except the bold on the HEADER cells. Since I didn't have your data, I had to make some by using SASHELP.PRDSALE and then fiddling with variable values until I got some missing values.[pre]
ods path sasuser.templat(update)
sashelp.tmplmst(read);
options missing=.;
data dfile;
set sashelp.prdsale;
datayear = year;
ageyrs = (today()-month)/365.25;
if year = 1994 and division = 'CONSUMER' then datayear = datayear - 2;
if division = 'CONSUMER' then ageyrs=ageyrs-5;
if division = 'EDUCATION' and prodtype='FURNITURE' then ageyrs=.;
run;
proc format;
value ageten low-<10 = 'Group 1'
10-20 = 'Group 2';
run;
** Modify the Minimal style to get rid of;
** all border lines, colors, etc;
proc template;
define style styles.XLStatistical;
parent = styles.Statistical;
style Body from Body/
background=_undef_;
style table from Output /
background=_undef_
foreground=_undef_
borderwidth=_undef_
font_size=10pt;
style Header from Header /
background=_undef_
foreground=_undef_
borderwidth=_undef_
font_size=10pt;
style RowHeader from RowHeader /
background=_undef_
foreground=_undef_
borderwidth=_undef_
font_size=10pt;
style Data from Data /
background=_undef_
foreground=_undef_
borderwidth=_undef_
font_size=10pt;
end;
run;
ods tagsets.excelxp file='c:\temp\use_style.xls'
style=styles.XLStatistical;
ods tagsets.excelxp options(sheet_name='All Heat Deaths');
options missing=0;
proc tabulate data=dfile f=comma9.;
class datayear ageyrs;
tables all*{s={tagattr='format:#,###,#00'}} ageyrs=' ',
all datayear=' ' / rts=20 misstext='0';
keylabel n=' ' all='Total';
format ageyrs ageten.;
run;
ods tagsets.excelxp close; [/pre]
Once I create styles.XLStatistical, I have to use in the ODS invocation for tagsets.excelxp.
That still doesn't address your zero as text issue, but I did check with the developers and they are working on getting an updated version of the ExcelXP tagset approved now. When they do, it will be posted here:
http://support.sas.com/rnd/base/topics/odsmarkup/
(scroll down the page an look for the heading
"Updated SAS 9.1 Tagsets to Download Individually" and you will find a place to download an updated tagset. Right now, the ExcelXP tagset is dated June 2006 -- that one still has the zero as text issue so you either have to wait for an updated one (should have an October or November date) or contact Tech Support to get the updated tagset before it's posted.
Once you have the updated tagset, you have to do just 3 things:
1) use an ODS PATH statement to point to your template storage location
2) run the code to update your template storage location with the new tagset for ExcelXP and the new style template
3) use the updated tagsets.excelxp tagset and the new style template
Using the ODS PATH statement will ensure that the updated tagset and style gets written to SASUSER.TEMPLAT template storage location. Since I don't know whether you have used the ODS PATH statement before, I included one in my code: [pre]
ods path sasuser.templat(update)
sashelp.tmplmst(read);[/pre]
Of course, if you don't want to use the SASUSER library, that's fine, you just have to put some other permanent location in your ODS PATH statement so ODS knows where to look for the updated templates.
cynthia
Click image to register for webinar
