I'm trying to convert some reports that use Proc Export to create multi-sheet Excel workbooks. Most users then use a macro or macros to apply formatting to the Excel file.
The Proc Export output is completely unformatted except for numeric formats (dates, currency, etc.). No background color, no borders, and the area outside the exported data is formatted (or non-formatted) the same way. Print Preview shows no cell borders in the data area or outside the data area of a spreadsheet.
I've tried this code that I found on this forum to create a template that somewhat mimics what Excel does:
define style styles.mymin;
parent = styles.Statistical;
replace Body from Body/
replace table from Output /
replace Header from Header /
replace RowHeader from RowHeader /
replace Data from Data /
When I look at output generated by the ExcelXP tagset and this style, the data portion looks like I would expect - the familiar Excel grid is there, but the area outside the data block is solid white, without the cell grid; it has the appearance of having a solid white background. Additionally, if I select a range of empty cells and apply "no fill" to it, it reverts to the "default" Excel look. So the entire spreadsheet is filled with some color, or there is some Fill setting there that gives the appearance of solid white fill. And Print Preview reveals that the Data area actually has light grey borders around and inside the range. This is confirmed in the XML file:
(right, top, and bottom borders are the same)
If I save the XML spreadsheet as an XLS and then re-open, the appearance does not change.
Is it possible to use ODS to create a spreadsheet that has no borders (except for ones I explicitly want around the header cells) and no fill. I understand, this isn't producing an actual Excel file, but an XML file that Excel can "play nice" with - just trying to get our output as close as possible to what is produced by our current Export + Excel Macro method.
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:
<Style ss:ID="Default" ss:Name="Normal">
<Font ss:FontName="Calibri" x:Family="Swiss" ssize="11" ss:Color="#000000"/>
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.
** Proc Export method to create multiple sheets;
proc sort data=sashelp.shoes out=Pshoes;
where region = 'Pacific';
by region subsidiary;
proc sort data=sashelp.shoes out=WEshoes;
where region = 'Western Europe';
by region subsidiary;
proc sort data=sashelp.shoes out=Drshoes;
where product contains 'Dress';
by region subsidiary;