Proc report to Excel - numbers get rounded up

[Taliah]

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

[Tom · Accepted solution]

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]

Worked great. Thank you!