DATA Step, Macro, Functions and more

Replicate accounting number format like Excel

Reply
New Contributor
Posts: 2

Replicate accounting number format like Excel

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!

Regular Contributor
Regular Contributor
Posts: 162

Re: Replicate accounting number format like Excel

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.

Super User
Posts: 5,518

Re: Replicate accounting number format like Excel

[ Edited ]

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;

Regular Contributor
Regular Contributor
Posts: 162

Re: Replicate accounting number format like Excel

Posted in reply to Astounding

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

Super User
Posts: 5,518

Re: Replicate accounting number format like Excel

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

Regular Contributor
Regular Contributor
Posts: 162

Re: Replicate accounting number format like Excel

[ Edited ]
Posted in reply to Astounding

 

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

Super User
Posts: 11,343

Re: Replicate accounting number format like Excel

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

Ask a Question
Discussion stats
  • 6 replies
  • 156 views
  • 4 likes
  • 4 in conversation