The SAS Output Delivery System and reporting techniques

Proc Tabulate rounding issues

Accepted Solution Solved
Reply
Occasional Contributor J_C
Occasional Contributor
Posts: 18
Accepted Solution

Proc Tabulate rounding issues

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
Super User
Posts: 11,343

Re: Proc Tabulate rounding issues

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

View solution in original post


All Replies
Super User
Posts: 19,855

Re: Proc Tabulate rounding issues

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

SAS Super FREQ
Posts: 8,868

Re: Proc Tabulate rounding issues

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

Occasional Contributor J_C
Occasional Contributor
Posts: 18

Re: Proc Tabulate rounding issues

Posted in reply to Cynthia_sas

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
Super User
Posts: 11,343

Re: Proc Tabulate rounding issues

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

Occasional Contributor J_C
Occasional Contributor
Posts: 18

Re: Proc Tabulate rounding issues

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?


Super User
Posts: 11,343

Re: Proc Tabulate rounding issues

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.

Occasional Contributor J_C
Occasional Contributor
Posts: 18

Re: Proc Tabulate rounding issues

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 1428 views
  • 3 likes
  • 4 in conversation