Contributor
Posts: 73

# format money into dimes rounding;display as 2 decimals

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?
Frequent Contributor
Posts: 95

## Re: format money into dimes rounding;display as 2 decimals

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;
Contributor
Posts: 73

## Re: format money into dimes rounding;display as 2 decimals

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!
Not applicable
Posts: 0

## Re: format money into dimes rounding;display as 2 decimals

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
Contributor
Posts: 73

## Re: format money into dimes rounding;display as 2 decimals

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?
Not applicable
Posts: 0

## Re: format money into dimes rounding;display as 2 decimals

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
Contributor
Posts: 73

## Re: format money into dimes rounding;display as 2 decimals

Good Idea. I think I'll try that approach. Thanks.
Frequent Contributor
Posts: 95

## Re: format money into dimes rounding;display as 2 decimals

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
Contributor
Posts: 73

## Re: format money into dimes rounding;display as 2 decimals

Interesting!!! You may have given me a reason to use Proc Report for the first time!! Thanks.
Discussion stats
• 8 replies
• 384 views
• 0 likes
• 3 in conversation