PROX EXPORT reverts numbers back to point-separated decimals

[RFLinnenbank]

Every time I export in a format that doesn't include formatting, SAS reverts the decimalpoint back to the American standard of '.'.

Unfortunately in Europe we use comma-separated decimals, so this can make quite the difference. I know it's possible to force format through a

DATA _NULL_

statement, but is there an easier way to have SAS Enterprise Guide / SAS use the comma-decimalseparator by default, or at least to force them to use the format given in the source table? Extra information:
This appears to only happen when I use an actual numeric format itself. If I use Currency-, Date-, or Time-formatting it will use the given format though.


Message was edited by: RFLinnenbank

[Cynthia_sas]

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