Replicate accounting number format like Excel

[abcda]

Is there a way to format a value as accounting to mimic the same format in Excel?

For example:

$     0.99

$     1.00 

$   10.00 

$ 100.00

(the currency sign is left aligned while the numeric value is right aligned)

Thanks!

[HB]

I don't think SAS has a format statement that will do that.

If you really wanted to, you could turn numbers into character values and append spaces and a dollar sign to the front to make them look like that. But that seems silly.

[Astounding]

While SAS doesn't have such a format, you can create your own.  Here's one way (you might want to add an extra blank after the dollar sign ... that's up to you):

proc format;

picture tst 0-< 10 = ' 9.99' (prefix='$  ')

10 - < 100 = ' 99.99' (prefix='$ ')

100- < 1000 = ' 999.99' (prefix='$');

run;

　

data test;

do i=0, 10, 50, 100; put i tst.; end;

run;

[HB]

If i do

data stuff;
   input mymoney;
datalines;
5.12
6.45
16.23
12.45
164.25
145.78
;
run;

proc format;
picture moneytest 
0 - < 10 = '    9.99' (prefix='$ ')
10 - < 100 = '   99.99' (prefix='$ ')
100- < 1000 = '	 999.99' (prefix='$ ');
run;

proc print;
	var mymoney;
	format mymoney moneytest.;
run;

I get

                                        Obs    mymoney

                                         1       $ 5.12
                                         2       $ 6.45
                                         3      $ 16.23
                                         4      $ 12.45
                                         5     $ 164.25
                                         6     $ 145.78


But if i do

data newmoney;
	set stuff;
	mycharmoney = put(mymoney,10.3);
	mycharmoney = '$' || mycharmoney;
run;

proc print;
	var mycharmoney;
run;

I get

                                       Obs    mycharmoney

                                        1     $     5.12
                                        2     $     6.45
                                        3     $    16.23
                                        4     $    12.45
                                        5     $   164.25
                                        6     $   145.78

[Astounding]

@HB

Notice that in constructing a picture format, the number of characters in the prefix is supposed to change.  (I inadvertently had removed one of those blanks, but have corrected that in the post above.)  Changing the size of the prefix will line up the dollar signs using the PICTURE format.

[HB]

@Astounding

I gotcha now. This "_9.00", "__9.00", "___9.00" doesn't seem to do any good. I think SAS collapses the spaces or something.

But this "$_", "$__", "$___" gets the job done. Cool.

[ballardw]

@HB The space difference you are seeing from @Astounding's solution is ODS output slightly reformatting things.

Is going to depend on active style, ods destination and/or font.

proc print data=newmoney;
   title "Simple Print";
	var mycharmoney /;
run;title;
proc print data=newmoney;
   title "With fixed width font ";
	var mycharmoney /style(data)=[fontfamily="Courier" ];
run; title;
proc print data=newmoney;
   title "With fixed width font and ASIS";
	var mycharmoney /style(data)=[fontfamily="Courier" ASIS=yes];
run;title;

Differing results:

Simple Print

Obs	mycharmoney
1	$ 5.12
2	$ 6.45
3	$ 16.23
4	$ 12.45
5	$ 164.25
6	$ 145.78

With fixed width font

Obs	mycharmoney
1	$ 5.12
2	$ 6.45
3	$ 16.23
4	$ 12.45
5	$ 164.25
6	$ 145.78

With fixed width font and ASIS

Obs	mycharmoney
1	$ 5.12
2	$ 6.45
3	$ 16.23
4	$ 12.45
5	$ 164.25
6	$ 145.78

I have no idea why the forum is rendering that last table with the < > bit but in the default HTML results viewer both the $ and Decimals align which I do not get with the simple print even of the string variable.