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?
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!
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.
do num = (0.1-1e10) to 0 by 123456700.89
, 1.234 to 1e10 by 123456700.89 ;
put num=comma19.2 num crazy. ;
Some of the output looks like[PRE] num=-9,999,999,999.90 $-9,999,999,999.9o
[/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.
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?
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.
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.