What you have discovered is that even though you have a SAS format for your variables, Excel does not always honor or use the SAS format. There is a way to pass a Microsoft format from SAS to EXCEL, however. The method that you use will vary, depending on whether you are using ODS HTML, ODS MSOFFICE2K or ODS TAGSETS.EXCELXP to create a file that Excel can open.
The following code shows you how to pass a Microsoft format to Excel for MSOFFICE2K and EXCELXP destinations. It doesn't matter whether you have a detail report or an ordered report or a grouped report. ODS just sends the MS format in a way that Excel can understand. Please note that HTML based destinations use the HTMLSTYLE attribute to pass the format, while XML based destinations use the TAGATTR attribute to pass the format. For example, here's the way that the HTML TD tag is written in the output file when you use the MSOFFICE2K destination:
<td class="Data" style=" text-align: right; mso-number-format:$#,###,###.##;"> $801</td>
and, here's the Spreadsheet Markup Language XML that is written in the output file when you use the EXCELXP destination:
<Style ss:ID="data__r1" ssarent="data__r">
<Protection ssrotected="1" />
<NumberFormat ss:Format="$#,###,###.##" />
You can use
ods html ...............;
proc report data=test;
define amount format=dollar8.2;
ods html close;
Hope this helps...
Let me know if you have nay issues