Proc report to Excel - numbers get rounded up

Taliah — Mon, 12 Aug 2019 14:28:24 GMT

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

Re: Proc report to Excel - numbers get rounded up

Tom — Mon, 12 Aug 2019 14:32:52 GMT

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.

Taliah — Tue, 13 Aug 2019 08:13:37 GMT

Worked great. Thank you!