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 ?
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
hi,
add this to your code to the column name under the define block of proc report :
style(Column)=data_date9
define order/format=comma10. style(column)=data_date9
did not work
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
Thanks for all your help
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.