Hi:
When I go into Excel -without- SAS and type some numbers into cells and then save the file as Excel 2003 XML, the ONLY style information in the file is this:
[pre]
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
[/pre]
To me, this says that Excel uses this style section for everything. I do not know what <Interior/> or <Borders/> empty tags are doing. Frequently, I find that empty tags in Excel Spreadsheet Markup Language XML are sometimes used as on/off switches.
When I use your template, I see the behavior I expect from SAS -- the cells in the tabular output have interior table lines and the rest of the worksheet does NOT have interior table lines. ODS TAGSETS.EXCELXP works by a different mechanism than PROC EXPORT. You can't compare the two, because it really is apples (PROC EXPORT writing to BINARY mode XLS file format ) and oranges (ODS writing to TEXT mode XML format files that Excel knows how to render).
The underlying assumptions of these two methods is different. The PROC EXPORT method has the underlying assumption that you want the data treated as -data- ...so, SAS has no way to store color and font information in a dataset, therefore, PROC EXPORT does not pass any color or font information to Excel.
On the other hand, one of the underlying assumptions of ODS TAGSETS.EXCELXP is that you WANT the ODS style template used to format the sheets. So, for example, if you took advantage of ODS TAGSETS.EXCELXP and the ability to use style templates for coloring the header and doing traffic lighting, etc, you would find that ODS can do a lot and you might not need your Excel macros anymore.
If you like the look and feel of PROC EXPORT, you -can- create multi-sheet workbooks with PROC EXPORT using code similar to what is shown below. Note, that for ease of understanding, I use PROC SORT to create 3 datasets and then write each dataset to a separate worksheet in the EXP_METHOD.XLS workbook. This is a true, binary Excel workbook with 3 sheets....and each sheet is named with the sheet= option.
Otherwise, if you want to use TAGSETS.EXCELXP, I'm not sure there's a style template way to do what you want to do. This is where I would recommend that you work with Tech Support. What you want to do may involve changing the XML that's written by TAGSETS.EXCELXP -- either to include an explicit Default style section in the XML or to reference a style section like this. Changing the tagset template is harder than changing a style template, which is another reason to work with Tech Support.
cynthia
[pre]
** Proc Export method to create multiple sheets;
proc sort data=sashelp.shoes out=Pshoes;
where region = 'Pacific';
by region subsidiary;
run;
proc sort data=sashelp.shoes out=WEshoes;
where region = 'Western Europe';
by region subsidiary;
run;
proc sort data=sashelp.shoes out=Drshoes;
where product contains 'Dress';
by region subsidiary;
run;
proc export data=Pshoes
outfile= "exp_method.xls"
dbms=excel2002 replace;
sheet="Pacific";
run;
proc export data=WEshoes
outfile= "exp_method.xls"
dbms=excel2002 replace;
sheet="West_Eur";
run;
proc export data=DRshoes
outfile= "exp_method.xls"
dbms=excel2002 replace;
sheet="Dressy";
run;
[/pre]
