02-22-2012 12:48 PM
I am using the msoffice2k_x tagset to create an Excel workbook with multiple sheets. I have been successful in creating the workbook, but I am having difficulty figuring out how to change a couple things on my sheets.
For instance, if a value is '1-2' in my SAS output, it ends up in Excel looking like 2-Jan. It has been changed to a date value. How do I get around that?
Also, the titles don't always align with my tables correctly in Excel. If the table is small, then the title will be much longer than the table. Is there a way to make it only the width of the table? Or at least to center it?
Any assistance is appreciated.
02-22-2012 03:51 PM
Excel has defaults for how it opens and renders the file that you create with ODS MSOFFICE2K_X. That destination creates an ASCII text file -- an HTML file -- that Excel knows how to open. But in the rendering department, Excel makes certain assumptions about column width, column type and column format that you may or may not want.
The way around this default Excel behavior is for you to send a specific Microsoft format instruction using the mso-number-format CSS style property. The way you send this style information to Excel from SAS is through the use of the HTMLSTYLE= style attribute.
There have been many previous forum postings about how to specify and use HTMLSTYLE in a STYLE= override; and the doc for MSOFFICE2K_X also shows an example under the topic "Formatting Data"
http://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/index.html#formatting (see how AGE and HEIGHT are each formatted with 2 decimal places)
In addition, this SAS Global Forum paper has extensive examples: