I have a query created where my output data table is showing 1089668.06 in the field - which is what I want. However, when I do a PROC REPORT to Excel, the value changes to 1089668.1. I'm not doing anything fancy in my PROC REPORT, mainly just want it to go to multiple excel sheets within one workbook. A portion of my code is below..
ods _all_ close;
ods listing close;
ods tagsets.ExcelXP path = 'network path'
file = 'FileName.xls' style=MINIMAL;
ods tagsets.ExcelXP
options(sheet_name='WorksheetName'
frozen_headers="yes"
absolute_column_width='8.75, 6.25, 10.57, 7.43, 15, 8.14, 30.86, 10.57'
autofit_height='yes');
PROC REPORT DATA=WORK.TblFinal (WHERE = (where clause))
STYLE(REPORT) = [BACKGROUND=WHITE FOREGROUND=BLACK]
STYLE(COLUMN) = [BACKGROUND=WHITE FOREGROUND=BLACK FONT_SIZE=2]
STYLE(HEADER) = [BACKGROUND=WHITE FOREGROUND=BLUE BOLD FONT_SIZE=2];
COLUMNS SKU Channel ShipToCountry Territory ForeignSalesValue Units Description RetailerCode
;
DEFINE SKU / 'SKU' DISPLAY ;
DEFINE Channel / 'Channel' DISPLAY;
DEFINE ShipToCountry / 'Ship To Country' DISPLAY;
DEFINE Territory / 'Territory' DISPLAY;
DEFINE ForeignSalesValue / 'Foreign Sales Value' DISPLAY;
/*style={tagattr='format:#,###,##0.00'} previously I had this, so I took it out, but it did not seem to matter*/
DEFINE Units / 'Units' DISPLAY style={tagattr='format:#,##0'};
DEFINE Description / 'Descrption' DISPLAY;
DEFINE RetailerCode / 'Retailer Code' DISPLAY;
;RUN;quit;
Hi:
I am not sure how you say that TAGATTR does not make a difference. Perhaps you need to work with Tech Support. If you run the attached code, you should see that the SAS format is used in the ODS HTML output, but that in the ExcelXP output, only when you use TAGATTR do you have control over the number of decimal places. Here's an example of the output produced by the simplified version of your program listed below.
cynthia
Shows that TAGATTR is needed:
SAS code:
ods _all_ close;
ods html file='c:\temp\test_decimal.html';
ods tagsets.ExcelXP path = 'c:\temp'
file = 'test_decimal.xml' style=MINIMAL;
options(sheet_name='test_decimal');
PROC REPORT DATA=sashelp.class nowd
STYLE(REPORT) = [BACKGROUND=WHITE FOREGROUND=BLACK]
STYLE(COLUMN) = [BACKGROUND=WHITE FOREGROUND=BLACK FONT_SIZE=2]
STYLE(HEADER) = [BACKGROUND=WHITE FOREGROUND=BLUE BOLD FONT_SIZE=2];
COLUMNS name age sex height weight;
define name / order;
define age / f=z6.2 'age with z6.2 SAS format';
DEFINE height / 'height with tagattr' DISPLAY f=6.2
style={tagattr='format:##0.00'} ;
DEFINE weight / 'weight without tagattr' DISPLAY f=6.2;
RUN;
ods _all_ close;
Hi:
I am not sure how you say that TAGATTR does not make a difference. Perhaps you need to work with Tech Support. If you run the attached code, you should see that the SAS format is used in the ODS HTML output, but that in the ExcelXP output, only when you use TAGATTR do you have control over the number of decimal places. Here's an example of the output produced by the simplified version of your program listed below.
cynthia
Shows that TAGATTR is needed:
SAS code:
ods _all_ close;
ods html file='c:\temp\test_decimal.html';
ods tagsets.ExcelXP path = 'c:\temp'
file = 'test_decimal.xml' style=MINIMAL;
options(sheet_name='test_decimal');
PROC REPORT DATA=sashelp.class nowd
STYLE(REPORT) = [BACKGROUND=WHITE FOREGROUND=BLACK]
STYLE(COLUMN) = [BACKGROUND=WHITE FOREGROUND=BLACK FONT_SIZE=2]
STYLE(HEADER) = [BACKGROUND=WHITE FOREGROUND=BLUE BOLD FONT_SIZE=2];
COLUMNS name age sex height weight;
define name / order;
define age / f=z6.2 'age with z6.2 SAS format';
DEFINE height / 'height with tagattr' DISPLAY f=6.2
style={tagattr='format:##0.00'} ;
DEFINE weight / 'weight without tagattr' DISPLAY f=6.2;
RUN;
ods _all_ close;
Cynthia,
Thank you for your quick response. When I stated the TAGATTR did not make a difference, based on how I was using it, it was not. However, once I looked at your code, I realized I did not have the f=10.2 along with it, which was needed.
Here's what I had, which did not work
DEFINE ForeignSalesValue / 'Foreign Sales Value' DISPLAY
style={tagattr='format:#,##0.00'};
Here's what I changed it to, which did work!
DEFINE ForeignSalesValue / 'Foreign Sales Value' DISPLAY f=10.2 style={tagattr='format:##0.00'};
Again, thank you!
...I've been trying to find exactly what the f= is, would you have anything to point me in the right direction?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.