Proc Report ODS to Excel

[newbi]

I have proc report with some of the data in Comma10. format.  When i output the report data in Excel i loose the comma formatting.

define order/ format = comma10.;

How can i resolve this issue ?

[asishgautam]

hi,

add this to your code to the column name under the define block of proc report :

style(Column)=data_date9

[newbi]

define order/format=comma10. style(column)=data_date9

did not work

[Cynthia_sas · Accepted solution]

Hi:

  I get an "Unable to find" note in my log when I try your code. Do you have code that possibly uses a custom style template or are you using a different destination for your output creation???

177  ods tagsets.excelxp file='c:\temp\testfmt.xml'
178      style=sasweb;
NOTE: Writing TAGSETS.EXCELXP Body file: c:\temp\testfmt.xml
NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.94, 09/09/12). Add
options(doc='help') to the ods statement for more information.
179  proc report data=sashelp.shoes nowd;
180    column region product sales;
181    define region /group;
182    define product /group;
183    define sales / sum style(Column)=data_date9;
184  run;
                
NOTE: Unable to find the "DATA_DATE9" style element. Default style attributes will be used.
NOTE: There were 395 observations read from the data set SASHELP.SHOES.
NOTE: PROCEDURE REPORT used (Total process time):
      real time           0.07 seconds
      cpu time            0.07 seconds

       
185  ods _all_ close;

  Generally with Excel, you are bound by the default format that Excel uses for numbers. However, if you are using TAGSETS.EXCELXP, you can use the TAGATTR method to send a Microsoft format to Excel for formatting numbers once the output is opened in Excel. The code below shows how to use a STYLE= override with TAGATTR= as the style override for TAGSETS.EXCELXP.

ods tagsets.excelxp file='c:\temp\useTagAttr.xml'

    style=sasweb;

proc report data=sashelp.shoes nowd;

  column region product sales;

  define region /group;

  define product /group;

  define sales / sum

         style(Column)={tagattr="Format:###,###,###.00"};

run;

ods _all_ close;

If you wanted to use ODS MSOFFICE2K to create a file for Excel, then you would use the HTMLSTYLE= override instead of TAGATTR. (My SGF paper has more examples of this. http://support.sas.com/resources/papers/proceedings11/266-2011.pdf )

cynthia

[newbi]

Thanks for all your help