BookmarkSubscribeRSS Feed
JohnH
Fluorite | Level 6
I have a strange formatting need. I need to get proc tabulate to format an average money amount into dimes, but display two decimals. For example, if the average money amount is 265,456,234.3645 then the formateed value needs to be $265,456,234.40 . I have tried picture formats and others, but I cannot get it to work. (One of my attempts is to try to get a character 0 into the picture format, just like a character M, but I do not know how to get 0 as a character into the picture format.) Does anyone have any clever idea on how to get a dimes rounding format printed out in two decimals?
8 REPLIES 8
1162
Calcite | Level 5
How about this?

data numbers;
input value;
format value2 dollar18.2;
value2=round(value,.1);
cards;
12.3456
98.7654
56.7890
265456234.3645
;
run;

proc print; run;
JohnH
Fluorite | Level 6
That works, but my problem is that the average is computed in proc tabulate. I could use an out= option, do what you are suggesting, then send it back to proc tabulate again, but I am looking for a clever formatting on the first proc tabulate table to solve the problem. The embarassing fact is that there is a formatting option in excel that does the job. I can't believe that EXCEL can trump SAS on this issue! Help, someone!
deleted_user
Not applicable
here is a version using lowcase "o" as the non-numeric in place of your second decimal place. Just change it to uppercase "O" to get closer to what you seek[pre]proc format ;
picture crazy (round)
-1e10 < 0= '000,000,000,001.1o'( prefix='$-' )
0- <1e10= '000,000,000,001.1o'( prefix='$' )
other = best17.
;
run;
data;
do num = (0.1-1e10) to 0 by 123456700.89
, 1.234 to 1e10 by 123456700.89 ;
put num=comma19.2 num crazy. ;
end;
run;
[/pre]
Some of the output looks like[PRE] num=-9,999,999,999.90 $-9,999,999,999.9o
num=-9,876,543,299.01 $-9,876,543,299.0o
num=-9,753,086,598.12 $-9,753,086,598.1o
num=-9,629,629,897.23 $-9,629,629,897.2o

num=-246,920,629.59 $-246,920,629.6o
num=-123,463,928.70 $-123,463,928.7o
num=-7,227.81 $-7,227.8o
num=1.23 $1.2o
num=123,456,702.12 $123,456,702.1o
num=246,913,403.01 $246,913,403.0o

num=3,950,614,429.71 $3,950,614,429.7o
num=4,074,071,130.60 $4,074,071,130.6o
num=4,197,527,831.49 $4,197,527,831.5o
num=4,320,984,532.38 $4,320,984,532.4o
[/PRE] you need to be careful to have enough room in the picture for the largest / widest values likely to turn up in your data.

Good luck

PeterC
JohnH
Fluorite | Level 6
Thanks for the idea of the letter "O", but I am producing tables for publication, so I really need a perfect zero in that position. Does anyone know how to enter the "character zero" into the cents position? Or, is there a way to change the use of 0's and 9's to something else in the picture format so I can get a 0 to show up in the picture?
deleted_user
Not applicable
Because the picture statement treats numeric characters as digit selectors and the suppression provided by the "noedit" option affects the whole picture string, it's difficult to follow a route seeking to treat the trailing zero as non-data. Instead, since it is for publication rather than real-rime response, perhaps use "some unique symbol" rather than a trailing "O" and postprocess replacing that "some unique symbol" with a true zero.

Good luck
PeterC
JohnH
Fluorite | Level 6
Good Idea. I think I'll try that approach. Thanks.
1162
Calcite | Level 5
This isn't a solution for Proc Tabulate, but it works for Proc Report. Could Proc Report be used instead? This solution uses one Proc Report. No additional datasets need be created and I think it's simpler than the formatting with special characters.

data numbers;
input value;
cards;
265456234.3643
265456234.3644
265456234.3645
265456234.3646
265456234.3647
;
run;

proc report data=numbers nowindows;
columns value rounded;
define value / analysis mean format=comma18.2;
define rounded / computed format=dollar18.2;
compute rounded;
rounded = round(_c1_,0.1);
endcomp;
run;

Output:
value rounded
265,456,234.36 $265,456,234.40
JohnH
Fluorite | Level 6
Interesting!!! You may have given me a reason to use Proc Report for the first time!! Thanks.

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
  • 8 replies
  • 1168 views
  • 0 likes
  • 3 in conversation