The SAS Output Delivery System and reporting techniques

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

Reply
N/A
Posts: 0

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

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.
SAS Super FREQ
Posts: 8,744

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

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" ssSmiley Tonguearent="data__r">
<Protection ssSmiley Tonguerotected="1" />
<NumberFormat ss:Format="$#,###,###.##" />
</Style>

<Cell ssSmiley FrustratedtyleID="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]
N/A
Posts: 0

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

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
Ask a Question
Discussion stats
  • 2 replies
  • 112 views
  • 0 likes
  • 2 in conversation