Hi:
If you use ODS HTML or an HTML-based destination to create a file for Excel, you can pass a Microsoft format to Excel via the HTMLSTYLE attribute. If you use ODS TAGSETS.EXCELXP (an XML-based destination) to create a file for Excel, you can pass a Microsoft format to Excel via the TAGATTR attribute. This is a slightly different approach than PROC EXPORT. EXPORT creates a binary Excel file, and Excel treats the numbers you have using its default formats which are not always what you want. When you use HTML or Spreadsheet Markup Language XML to create ASCII text files that Excel can open, you have the ability to pass a Microsoft format via a style override -- that's because for either HTML or XML -- Excel will accept the format specification passed as a style change.
See these topics for more help:
http://support.sas.com/kb/32/414.html
http://www2.sas.com/proceedings/sugi28/012-28.pdf (page 2 about HTMLSTYLE)
http://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html#formats
http://support.sas.com/rnd/base/ods/templateFAQ/Excel1.pdf
http://support.sas.com/kb/34/242.html (tagattr example)
http://support.sas.com/resources/papers/proceedings09/016-2009.pdf (page 21 for TAGATTR)
The issue is that Excel only honors some numeric formats. For example, if you use a number (like zip code) with leading zeroes, SAS can show that number with the Z format, but Microsoft ignores the Z format. Sometimes, your big numbers turn into scientific notation. Some dates will work, others won't. Some monetary formats will work, some won't. I've never actually figured out how and when Excel decides to ignore my numeric format. But depending on how I create the file for Excel -- either HTML or TAGSETS.EXCELXP, I can pass in a Microsoft format to get Excel on the right path.
cynthia