I am using ods tagsets.ExcelXP to print a report to excel (using xml; below is only a small part of the output, which contains many sheets).
When the numbers are large, the numbers get rounded up. Is there a way to prevent this rounding up.
The code I'm using -
ods tagsets.ExcelXP file…\TEST..xml" style=sansprinter;
ods tagsets.ExcelXP options(embedded_titles='yes' embedded_footnotes='yes' sheet_name="TEST1"sheet_interval='none');
proc report data=A nowd split="*" spanrows
style (header)={just=c background=GGR foreground=black}
style (report)={bordercolor=black borderbottomwidth=3pt borderleftwidth=3pt
borderrightwidth=3pt};
column NAME SUM;
define NAME / display ' ' style=[just=r cellwidth=2in];
define SUM / analysis 'SUM' style=[just=l cellwidth=6in
tagattr='format:####,###,###.###'];
run; quit;
ods tagsets.ExcelXP close;
When the value of SUM in the data file is -371675614.400021
The value printed in the report is -371680000
I tried different formats and the result is the same.
Thank you,
Tali
PROC REPORT is writing text and ODS EXCEL.XP is converting it into XML that Excel can read.
You have told Excel how to display the value using the TAGATTR style attribute.
But you didn't tell PROC REPORT how to format the value when converting the number into text for the report.
Try adding a FORMAT=14.3 to your DEFINE statement for that column.
PROC REPORT is writing text and ODS EXCEL.XP is converting it into XML that Excel can read.
You have told Excel how to display the value using the TAGATTR style attribute.
But you didn't tell PROC REPORT how to format the value when converting the number into text for the report.
Try adding a FORMAT=14.3 to your DEFINE statement for that column.
Worked great. Thank you!
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.