ODS and Base Reporting

Build reports by using ODS to create HTML, PDF, RTF, Excel, text reports and more!
BookmarkSubscribeRSS Feed
šŸ”’ This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Taliah
Quartz | Level 8

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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.

View solution in original post

2 REPLIES 2
Tom
Super User Tom
Super User

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
Quartz | Level 8

Worked great. Thank you!

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1923 views
  • 2 likes
  • 2 in conversation