BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
J_C
Calcite | Level 5 J_C
Calcite | Level 5

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,

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

7 REPLIES 7
Reeza
Super User

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

Cynthia_sas
SAS Super FREQ

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

J_C
Calcite | Level 5 J_C
Calcite | Level 5

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).

ballardw
Super User

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

J_C
Calcite | Level 5 J_C
Calcite | Level 5

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?


ballardw
Super User

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.

J_C
Calcite | Level 5 J_C
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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