SAS Picture formats - without fear (or tears)

[AndersS]

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

[ballardw]

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.

[AndersS]

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.

Below is your program:

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!

/Br AndersS

[Reeza]

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

[AndersS]

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

[AndersS · Accepted solution]

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

[ballardw]

---

@AndersS wrote:

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

[AndersS]

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+"