turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- ODS and Base Reporting
- /
- Proc Tabulate rounding issues

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-19-2014 12:10 PM

Hi,

In the dataset, the format/informat of variable "auth_pct" and "os_pct" are best8.6 to keep them in decimals (conversion to percentage within proc tabulate).

When creating an output to Excel (ODS tagset.excel), it automatically rounds the the precentages with zero decimal points but I request one decimal point.

The excel format I am trying to implement is 0.0% which works, but it rounds and all my decimal place are zero (e.g.12.0%, instead of 11.7%)

Here is the procedure:

proc tabulate data=rwork.past_qtr missing order=data ;

where business_line="&business_line";

class period_desc credit_type_rank asset_group_desc;

var auth_total os_total auth_below_83 os_below_83 auth_pct os_pct;

format credit_type_rank credit_type_code.;

table period_desc*credit_type_rank*asset_group_desc,

(auth_total='Sum of Total Authorized'*sum=''*[style=[tagattr='format:#,##0,,']]

os_total='Sum of Total Utilized'*sum=''*[style=[tagattr='format:#,##0,,']]

auth_below_83='Sum of Authorized Below IG83'*sum=''*[style=[tagattr='format:#,##0,,']]

os_below_83='Sum of Utilized Below IG83'*sum=''*[style=[tagattr='format:#,##0,,']]

**auth_pct='Sum of % Authorized Below IG83'*sum=''*[style=[tagattr='format:0.0%']]**

**os_pct='Sum of % Utilized Below IG83'*sum=''*[style=[tagattr='format:0.0%']]**

);

label period_desc='Date' credit_type_rank='Credit Type' asset_group_desc='Asset Group';

run;

What goes on "behind the scene" of the procedure and why does it round it automatically?

Thanks,

Accepted Solutions

Solution

09-22-2014
11:40 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-22-2014 11:40 AM

Since you didn't provide an explicit format for the statistics proc tabulate defaults to 2 decimals for sum.

Instead of :

auth_total='Sum of Total Authorized'*sum=''*[style=[tagattr='format:#,##0,,']

for html try:

auth_total='Sum of Total Authorized'*sum=''*f=f8.6

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-19-2014 02:05 PM

I believe you've specified that they should end in 0, but putting 0 in the format, otherwise you put a # sign instead.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-19-2014 05:03 PM

Hi: Is tabulate doing the rounding or is Excel doing the rounding. Your TAGATTR is an instruction to EXCEL, not to TABULATE -- so TABULATE is NOT doing the rounding. How you can tell is to send the output to ODS HTML or ODS PDF and look at the numbers as SAS is creating them. Also, your instruction is telling Excel to multiply the numbers by 100, so, depending on what your initial number is, you might be specify the format wrong. For example, if my number is .123 and I want to see .123 in Excel, (tell Excel NOT to multiply by 100), then the tagattr would be:

**format:##0.0\%** BUT, if I want the .123 to be displayed as 12.3, then my tagattr would be** format:##0.0%** WITHOUT the slash.

cynthia

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Cynthia_sas

09-22-2014 09:35 AM

Hi Cynthia,

Thanks for your response. I sent the output to ODS HTML and the numbers are also rounded (e.g. dataset=0.123456, HTML output=0.12).

Solution

09-22-2014
11:40 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-22-2014 11:40 AM

Since you didn't provide an explicit format for the statistics proc tabulate defaults to 2 decimals for sum.

Instead of :

auth_total='Sum of Total Authorized'*sum=''*[style=[tagattr='format:#,##0,,']

for html try:

auth_total='Sum of Total Authorized'*sum=''*f=f8.6

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

09-22-2014 04:33 PM

Thank you ballardw.

If my understanding is correct (correct me if I am wrong), "style=tagattr" is a format in Excel while "f=" is a format for proc tabulate?

Is the default best12.2 for the proc tabulate format?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-22-2014 05:17 PM

Yes you are correct on the f= vs tagattr for location.

I find that the format for tabulate depends on my customers as much as anything as well as the data I am displaying. I am often reporting counts (n=) and use a comma or f6.0 format as decimals look funny when they are all 0. Also unless you provide a style override the format width is used to some extent to set column width. So if you don't need 12 print columns then a smaller overall width might look better. If you a looking for a default format and 12.2 works for most of your data then it is a good choice. You can still provide an override in the actual statistic as needed.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

09-22-2014 05:24 PM

Perfect! Thanks for your help and the explanation.

Since we usually work in $billion, best12. has never been an issue and is convenient for us. Only when we got to these percentages did we run into problems.

Greatly appreciate the help.