How to make a format for monetary values

[deleted_user]

I know there is the DOLLARxx.y format for monetary data, and there is also the NEGPAREN format for negative data.

Question: How might I combine these two in a format statement so that I could then display a negative (or minus) monetary amount like this: $ (1234.56) ?

I think I should use a FORMAT statement, but haven't been successful in making one yet.

Can someone please help? That would be great!

[Cynthia_sas]

Hi,
There are different kinds of formats. The ones that you define with an INVALUE statement are more accurately called INFORMATS and those are meant to be used to read data INTO SAS.

The formats that you define with a VALUE statement are called FORMATS and those are meant to be used to display data from SAS into a certain format. For example:
[pre]
proc format;
value $team 'UNC' = 'Tar Heels'
'DUKE' = 'Blue Devils'
'NCSU' = 'Wolfpack'
'NMSU' = 'Aggies'
'UTEP' = 'Miners';
run;
[/pre]
so first you define the format, then you USE the format in a format statement:
[pre]
format school $team.;
[/pre]

Then, there's a type of format called a PICTURE format. You can read about it in the PROC FORMAT documentation under the description for the PICTURE statement. You still USE the created format in a FORMAT statement -- what's different is how you DEFINE the format -- with a PICTURE statement. Some folks like to think of the VALUE type format as being used for "recoding" or setting categories; while they think of the PICTURE type format as "masking" or making a "pattern" for the display format. For example, this comes straight from the documentation on a PICTURE statement:
[pre]
proc format;
picture pay low-high='000,009.99' (prefix='$');
run;
[/pre]
and the format statement would be:
[pre]
format salary pay.;
[/pre]

In the above PICTURE or mask for the salary variable, 2 decimal places will always be shown and there will always be -something- in the 1's place -- for example, the numeric value .23 would be displayed as $0.23 with the above format. The documentation is very good on when and how you use the 0 digit selectors vs the non-zero digit selectors and about the prefix possibilities.

In addition to the documentation, Tech Support has some samples and FAQ:
http://support.sas.com/ctx/samples/index.jsp?sid=910&tab=code
http://support.sas.com/ctx/samples/index.jsp?sid=62&tab=code
http://support.sas.com/faq/003/FAQ00320.html
http://support.sas.com/ctx/samples/index.jsp?sid=847&tab=code
http://support.sas.com/ctx/samples/index.jsp?sid=154&tab=details

Lots of examples!
cynthia

[deleted_user]

And again and again, THANK YOU, Cynthia! The first link you provided was quite similar to what I was looking for, although I'd still like to be able to put parentheses around my negative monetary amounts. Is there anything similar to PREFIX for placing something at the end of a value?

[Cynthia_sas]

Hi:
This is a modification of that Tech Support program:
[pre]

PROC FORMAT;
PICTURE ACCTDOL LOW-<0 = '000,000,009.99)' (PREFIX='$(')
0 -HIGH = '000,000,009.99 ' (PREFIX='$');

PICTURE DOL LOW-<0 = '000,000,009.99' (PREFIX='$-')
0 -HIGH = '000,000,009.99' (PREFIX='$');
RUN;

DATA testfmt;
INFILE DATALINES;
INPUT X ;
ACCTDOL = X;
DOL=X;
FORMAT x best14.4 ACCTDOL ACCTDOL. DOL DOL. ;
return;
DATALINES;
12345
0
-12345
-187.65
187.65
.23
.231
.234
.235
.236
1.234
101.23
1.1E6
;
RUN;

PROC PRINT data=testfmt;
TITLE 'Using Picture Formats';
VAR X ACCTDOL DOL ;
RUN;

[/pre]

Note how the close paren goes into the Picture and you just use a different prefix for the negative dollar range [low-<0 means less than 0 or negative values], where [0-HIGH] means all the positive values.

This is the output from the above program:
[pre]
Using Picture Formats

Obs X ACCTDOL DOL

1 12345 $12,345.00 $12,345.00
2 0 $0.00 $0.00
3 -12345 $(12,345.00) $-12,345.00
4 -187.65 $(187.65) $-187.65
5 187.65 $187.65 $187.65
6 0.23 $0.23 $0.23
7 0.231 $0.23 $0.23
8 0.234 $0.23 $0.23
9 0.235 $0.23 $0.23
10 0.236 $0.23 $0.23
11 1.234 $1.23 $1.23
12 101.23 $101.23 $101.23
13 1100000 $1,100,000.00 $1,100,000.00
[/pre]

The documentation has a good explanation about how the Picture is filled from RIGHT to LEFT. So 1) your picture has to be big enough for the entire "mask" including characters like leading $ and 2) if you're not careful to make it big enough you get truncation and 3) by default, you don't get rounding. The doc talks about that.

cynthia

[LawrenceHW]

Great example Cynthia.

PICTURE formats can be (in my experience) quite hard to explain. But they are very, very useful. I find them invaluable for special date/time presentations.

I think the main thing that gets people confused is the fact the placeholders are numeric values, so everyone automatically thinks of them being the values. When they're not!

I would concur with Cynthia on using the ROUND option. It can cause some unusual results if you don't use it.

[deleted_user]

Thanks Cynthia for your always welcome assistance. Here's another question.... I notice that the dollar sign "floats" in the field according to the size of the monetary value.... how do I create a format that keeps the dollar sign in a "fixed" olace in the field?

[Cynthia_sas]

Hi:
It sticks in my mind that this is how PICTURE works and you can't change it. The FILL option fills AFTER the picture has been applied (so, for example, you could create a mask of
[pre]
*******$12,345.00
[/pre]

But you have gotten the last neuron of my PICTURE knowledge. If I really, really, had to get a $ in the leftmost position, for LISTING only, I'd create a character variable from the amount and substring or concatenate the $ into the new character variable. Another possibility is to NOT show ANY dollar signs in the report rows and just show a $ on the LAST total row or at the bottom of the report -- which you can do with PROC REPORT and a CALL DEFINE for format on the RBREAK row. Maybe somebody else has a better idea or you could try Tech Support.

[pre]
Using FILL and Creating a Character Variable

Obs X flDOL other

1 12345 ****$12,345.00 $ 12,345.00
2 -12345 ***$-12,345.00 $ (12,345.00)
3 -187.65 ******$-187.65 $ (187.65)
4 187.65 *******$187.65 $ 187.65
5 1100000 *$1,100,000.00 $ 1,100,000.00
6 11100000 $11,100,000.00 $11,100,000.00

[/pre]

Personally, I don't like how the OTHER column looks in the output. And, in a proportional spaced font, such as that used with
HTML, RTF, etc, I doubt that it would all line up, anyway. Which means that as a stored process, even if you LIKE the way it looks in the ASCII output version,
it will NOT look that same way in EG or the SAS Add-in or Web Report Studio.

cynthia

[pre]
PROC FORMAT;
PICTURE OTHR (max=15) LOW-<0 = '000,000,009.99)' (PREFIX='(' FILL=' ' )
0 -HIGH = '000,000,009.99 ' (FILL=' ' );

PICTURE flDOL (max=15) LOW-<0 = '000,000,009.99' (PREFIX='$-' fill='*')
0 -HIGH = '000,000,009.99' (PREFIX='$' fill='*');
RUN;

DATA testfmt;
length other $15;
INFILE DATALINES;
INPUT X ;

flDOL = X;
other = put(x,othr.);
substr(other,1,1) = '$';
FORMAT x best14.4 flDOL flDOL.;
return;
DATALINES;
12345
-12345
-187.65
187.65
1.1E6
1.11E7
;
RUN;

PROC PRINT data=testfmt;
TITLE 'Using FILL and Creating a Character Variable';
VAR X flDOL other;
RUN;
[/pre]

[deleted_user]

That makes sense to me.... I removed the dollar sign, and it looks A LOT better. Since this report I'm doing is for our finance/accounting people, I certainly hope they realize that certain columns of info are monetary.....

Thanks!