Pyrite | Level 9

## SAS Picture formats - without fear (or tears)

SAS Picture formats – without fear (or tears)

The PICTURE format is part of the user-defined formats that you can create using PROC FORMAT.

A simple example is:  picture pay

low-high = '000,009.99' (prefix='\$');
This format means that eight (8) digits can be displayed, including the prefix.  If the number is too big, the prefix is omitted –” You have to guess…”

The problem:  When the number is too big, and probably is important to the user, only the last digits are used. These are the ones of least importance.
data work.test;
do i= 1 to 10;
num= 1.234567890*10**(i-4);
put num pay.;
end;
run;

The result in SAS Log:

\$0.00

\$0.01

\$0.12

\$1.23

\$12.34

\$123.45

\$1,234.56

\$12,345.67

123,456.78  ßß  Dollar? Pound ??

234,567.89  ßß  ERROR – Where did 1 Million dollars go?

One solution: Define an ordinary format, using the VALUE statement, and imbed the PICTURE format in this.
Below is a simple example. When the number is outside the range of the picture format, ordinary formats will be used instead. The risk that any number is shown in the wrong way, is much smaller.

proc format;

picture pay

low-high='000,009.99' (prefix='\$');

value goodpay

low - 0.01   = [comma8.]

0.01 - 99999 = [pay.]      /* The pay defined above. */

99999-high   = [comma8.];
run;

data work.test;

do i= 1 to 12;

num= 1.234567890*10**(i-4);

put num pay.  "   "  num goodpay.;

end;

run;

The result from SAS log, put in a table:

 pay Comment to PAY format goodpay Comment to goodpay \$0.00 0 \$0.01 \$0.01 \$0.12 \$0.12 \$1.23 \$1.23 \$12.34 \$12.34 \$123.45 \$123.45 \$1,234.56 \$1,234.56 \$12,345.67 \$12,345.67 123,456.78 Dollar or Pound? 123,457 Dollar or pound? 234,567.89 One million missing 1234568 Correctly rounded 345,678.89 10 millions missing 12345679 Correctly rounded 456,788.99 100 millions missing 1.2346E8 Correct thanks to E-format

I hope that this may be of some use.

/Br Anders Skollermo

Anders Sköllermo (Skollermo in English)
1 ACCEPTED SOLUTION

Accepted Solutions
Pyrite | Level 9

## Re: SAS Picture formats - without fear (or tears)

Hi!    I slightly better solution is:

proc format;  picture pay  low-high= '000,009.99' (prefix='\$');

value goodpay  low - 0.01 = [E10.4]
0.01 - 999999 = [pay10.] /* The pay defined above. */
999999 - high = [E10.4]; run;

Note that the maximum number in "000,009.99" is 999999 (the digit 9, six times).
That should be specified above (or the smaller number 99999) as the limits. If we use 9999999 as the limits big numbers will be displayed incorrectly!

Basically I think that the Picture format code should be changed, to avoid mistakes of this kind.

(A friend of mine found an error of this kind in the the sickness damage reports trhat he received from the IT-department. The value  1234.567 Msek  was displayed as 234.567 Msek - it could have cost him his job, if he had not found the error).  / Br AndersS

Anders Sköllermo (Skollermo in English)
7 REPLIES 7
Super User

## Re: SAS Picture formats - without fear (or tears)

Better is to define Ranges that make sense for the purpose.

Consider:

```proc format ;
picture  mypay
low -<1000000 = '0,000,009.99' (prefix='\$')
1000000 -<1000000000='009.9M'
(prefix='\$' mult=.00001)
;
run;

data test;
do i= 1 to 12;
num= 1.234567890*10**(i-4);
put num mypay.;
end;
run; ```

Result in the log:

```       \$0.00
\$0.01
\$0.12
\$1.23
\$12.34
\$123.45
\$1,234.56
\$12,345.67
\$123,456.78
\$1.2M
\$12.3M
\$123.4M

```

The Mult option multiplies the value to produce something a bit smaller and then you add a character, in this case and M to the format to display values in Millions repeat as needed for billion or trillion with smaller values of the multiplier. There is a limit from the precision of the decimal value is going to bottom out around 13 or 14 decimal points.

One thing to consider with decimal points is how critical or useful they are given the range of the value. If we are talking values in Millions of dollars for a report just how often is 0.01 dollars going to be real informative. IF needed, you make the format longer. but if you are willing to accept values like 1.2346E8 then rounding and shifting decimals to maintain proper placement of a currency symbol I would say is less disruptive when reading values.

Pyrite | Level 9

## Re: SAS Picture formats - without fear (or tears)

Hi!  The aim of my paper, was to shown the risk getting the wrong numbers in the SAS log. And also to show a fast and simple solution. That is one way NOT to get wrong numbers.

proc format ;
picture mypay
low -< 1E6 = '0,000,009.99' (prefix='\$')
1E6 -< 1E9 = '009.9M' (prefix='\$' mult= 1E-5) ;  run;

data test;
do i= 1 to 16;
num= 1.234567890*10**(i-4);
put num mypay.;
end;  run;

1) Many users will probably NOT understand that they must use mult=1E-5  to get data correct. (1E-6 will give incorrect values). Basically 6 (because of M) minus one  (because of .9) gives 5, i.e. 1E-5.

2) With  '009.99M' I think you must use  (prefix='\$' mult= 1E-4)

My aim was to show a simple and always correct solution.

Your solution requires that the user is "Awake and knowing what he/she does". My experience (myself and other users) is that that is not the case.

Nice solution!

Anders Sköllermo (Skollermo in English)
Super User

## Re: SAS Picture formats - without fear (or tears)

This doesn't seem like a question, more like a discussion? Is it ok if I mark this as not needing an answer? (PS you can also do that when posting the question).
Pyrite | Level 9

## Re: SAS Picture formats - without fear (or tears)

Hi! That is QUITE OK.   My intention was to show the usefulness of combining VALUE and PICTURE formats. And to avoid using a format that produces quite incorrect values.  MANY THANKS. /Br AndersS

Anders Sköllermo (Skollermo in English)
Pyrite | Level 9

## Re: SAS Picture formats - without fear (or tears)

Hi!    I slightly better solution is:

proc format;  picture pay  low-high= '000,009.99' (prefix='\$');

value goodpay  low - 0.01 = [E10.4]
0.01 - 999999 = [pay10.] /* The pay defined above. */
999999 - high = [E10.4]; run;

Note that the maximum number in "000,009.99" is 999999 (the digit 9, six times).
That should be specified above (or the smaller number 99999) as the limits. If we use 9999999 as the limits big numbers will be displayed incorrectly!

Basically I think that the Picture format code should be changed, to avoid mistakes of this kind.

(A friend of mine found an error of this kind in the the sickness damage reports trhat he received from the IT-department. The value  1234.567 Msek  was displayed as 234.567 Msek - it could have cost him his job, if he had not found the error).  / Br AndersS

Anders Sköllermo (Skollermo in English)
Super User

## Re: SAS Picture formats - without fear (or tears)

Hi!    I slightly better solution is:

proc format;  picture pay  low-high= '000,009.99' (prefix='\$');

value goodpay  low - 0.01 = [E10.4]
0.01 - 999999 = [pay10.] /* The pay defined above. */
999999 - high = [E10.4]; run;

Note that the maximum number in "000,009.99" is 999999 (the digit 9, six times).
That should be specified above (or the smaller number 99999) as the limits. If we use 9999999 as the limits big numbers will be displayed incorrectly!

Basically I think that the Picture format code should be changed, to avoid mistakes of this kind.

(A friend of mine found an error of this kind in the the sickness damage reports trhat he received from the IT-department. The value  1234.567 Msek  was displayed as 234.567 Msek - it could have cost him his job, if he had not found the error).  / Br AndersS

Please post code into code or text boxes. This forum inserts so much garbage I hate cleaning up stuff pasted into the main window.

When I started with SAS in version 5 this format would throw an error:

```proc format;
value goodpay
low - 0.01 = [E10.4]
0.01 - 999999 = [pay10.] /* The pay defined above. */
999999 - high = [E10.4];
run;
```

because it includes two values in different ranges: 0.01 and 999999. That doesn't throw an error now but anytime you have a range value repeated it is possible to get unexpected results.

I also think you need to consider what the purpose of a display format is. If the purpose is to display currency, then it should handle currency values. If the purpose is to show "all values" then it likely shouldn't be a currency format but something like best16. or 32.15. If I'm writing a currency format all the values should be currency or possibly a code value that would display other information.

And what does your proposed format above display for some not really problematic values? Such as why does 0 display as =0.000E+00 ? Wouldn't it make more sense in general to show \$0.00

Pyrite | Level 9

## Re: SAS Picture formats - without fear (or tears)

AGAIN: The aim of my paper, was to show the risk of getting the wrong numbers in the SAS log. And also to show a fast and simple solution. That is one way NOT to get wrong numbers.

There are better formats. Some other solutions, that are better than my original, are shown in the discussion. Many thanks for that!

My experience of writing computer programs, is that the code shalll be as clear and correct as possible. It should work, in a decent way, even with odd data. Minor problems in the output/ printing can always be solved by the user - BUT NOT if the number is entirely false, which sometimes happens with the Picture format.

With these lines I want to leave this discussion.

/Br AndersS - SAS user since 1981 (SAS79) - "Both Age and IQ are 75+"

Anders Sköllermo (Skollermo in English)
Discussion stats
• 7 replies
• 494 views
• 1 like
• 3 in conversation