Pl help me by using ODS to XLS the currency col should displ $

[deleted_user]

When i am trying to use proc report to xls through ODS , one variable output format , should display ex: $1123.24, But its not displaying " $" in xls ouput , It displaying only 1123.24 , .

Please guide me to get the the result output.
Thanks in advance.

[Cynthia_sas]

Hi,
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:
[pre]
<td class="Data" style=" text-align: right; mso-number-format:$#,###,###.##;"> $801</td>
[/pre]

and, here's the Spreadsheet Markup Language XML that is written in the output file when you use the EXCELXP destination:
[pre]
<Style ss:ID="data__r1" ss:Parent="data__r">
<Protection ss:Protected="1" />
<NumberFormat ss:Format="$#,###,###.##" />
</Style>

<Cell ss:StyleID="data__r1" ss:Index="3"><Data ss:Type="Number">801</Data></Cell>
[/pre]

For more information about Microsoft formats and how to specify them, consult this paper by Chevell Parker, which has some common MS format examples http://support.sas.com/rnd/base/ods/templateFAQ/Excel1.pdf
or consult the Microsoft documentation.

cynthia
[pre]
ods msoffice2k file='c:\temp\curr_mso.xls' style=sasweb;
proc report data=sashelp.shoes(obs=5) nowd;
column region product sales ;
define region / display;
define product / display;
define sales / sum
style={htmlstyle="mso-number-format:$#,###,###.##"};
run;
ods msoffice2k close;

ods tagsets.excelxp file='c:\temp\curr_xp.xls' style=sasweb;
proc report data=sashelp.shoes(obs=5) nowd;
column region product sales ;
define region / order;
define product / order;
define sales / sum
style={tagattr="$#,###,###.##"};
run;
ods tagsets.excelxp close;
[/pre]

[deleted_user]

Hi,
You can use
ods html ...............;
proc report data=test;
columns....
define amount format=dollar8.2;
;;;
;;;;
run;
ods html close;
Hope this helps...
Let me know if you have nay issues
Sireesha