topic Re: Replicate accounting number format like Excel in SAS Programming

Replicate accounting number format like Excel

abcda — Mon, 28 Aug 2017 16:56:44 GMT

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!

Re: Replicate accounting number format like Excel

HB — Mon, 28 Aug 2017 17:43:49 GMT

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.

Re: Replicate accounting number format like Excel

Astounding — Mon, 28 Aug 2017 19:25:24 GMT

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;

Re: Replicate accounting number format like Excel

HB — Mon, 28 Aug 2017 19:22:55 GMT

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

Re: Replicate accounting number format like Excel

Astounding — Mon, 28 Aug 2017 19:27:49 GMT

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

Re: Replicate accounting number format like Excel

HB — Mon, 28 Aug 2017 19:45:06 GMT

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

Re: Replicate accounting number format like Excel

ballardw — Mon, 28 Aug 2017 19:57:16 GMT

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