SAS Dollar Format – Formatting Numbers as Dollars in SAS Dataset

In SAS, we can format numbers as dollars very easily using the built-in SAS DOLLARw.d format or creating our own format with PROC Format.

/* Using Built-in SAS DOLLARw.d format */
data data_new;
    format dollar_amount dollar10.2;
    set data;
run;

/* Creating our own dollar format */

proc format;
   picture our_dollar_format 
       low           - -1000000000 = '00,000,000,009.9 B' (prefix='-$' mult=.000000001)
       -1000000000 < - -1000000    = '00,000,009.9 M'     (prefix='-$' mult=.000001)
       -1000000    < - -1000       = '009.9 K'            (prefix='-$' mult=.01)
       -1000       - < 0           = '009.99'             (prefix='-$')
        0          - < 1000        = '009.99'             (prefix='$')
        1000       - < 1000000     = '009.9 K'            (prefix='$' mult=.01)
        1000000    - < 1000000000  = '00,000,009.9 M'     (prefix='$' mult=.000001)
        1000000000 -   high        = '00,000,000,009.9 B' (prefix='$' mult=.000000001)
;
run;

data data_new;
    format dollar_amount our_dollar_format;
    set data;
run;

When working with data which represents money, many times we want to display the data in a way that makes sense. For money, this means adding dollar signs, commas, and other things that makes the financial data more readable.

In SAS, we can use the built-in DOLLARw.d format to format numeric values with a leading dollar sign, a comma that separates every three digits, and a period that separates the decimal fraction.

Let’s say we have some data which represents a dollar amount. We can use the DOLLARw.d format to format the data to show the data in dollars.

data data;
    format num dollar16.2;
    input num;
    datalines;
10382741.23
  817293.12
     754.89
     340.40
   78701.28
23074813.74
 6431782.00
    4832.93
;
run;

              num
1  $10,382,741.23
2     $817,293.12
3         $754.89
4         $340.40
5      $78,701.28
6  $23,074,813.74
7   $6,431,782.00
8       $4,832.93

The downside of using the built-in dollar format is you need to know how big the biggest number is in your dataset, or else the format won’t apply.

For example, if we used the Dollar10.2 format, the first and sixth numbers wouldn’t be formatted correctly.

Creating a User-Defined SAS Dollar Format with PROC Format

With PROC format, we are able to create user-defined number formats so our data can be displayed cleaner. One such format is a currency format, or in particular, a dollar format.

We could use the standard SAS dollar format, but if you’ve ever worked with the built-in SAS formats, sometimes you want a little more.

With PROC format, we can build our own dollar format.

When viewing financial data, personally, I like to convert what I’m looking at into thousands, millions or billions, depending on the value of the number.

We can create a format for this easily with PROC format in the following way. We will create a picture format for displaying dollars with the following SAS code.

proc format;
   picture our_dollar_format 
       low           - -1000000000 = '00,000,000,009.9 B' (prefix='-$' mult=.000000001)
       -1000000000 < - -1000000    = '00,000,009.9 M'     (prefix='-$' mult=.000001)
       -1000000    < - -1000       = '009.9 K'            (prefix='-$' mult=.01)
       -1000       - < 0           = '009.99'             (prefix='-$')
        0          - < 1000        = '009.99'             (prefix='$')
        1000       - < 1000000     = '009.9 K'            (prefix='$' mult=.01)
        1000000    - < 1000000000  = '00,000,009.9 M'     (prefix='$' mult=.000001)
        1000000000 -   high        = '00,000,000,009.9 B' (prefix='$' mult=.000000001)
;
run;

The “prefix” option adds the “$” sign to the number, and the “mult” option ensures our number is scaled appropriately.

Let’s see how “our_dollar_format” is applied in practice.

data data;
    format num our_dollar_format.;
    input num;
    datalines;
10382741.23
  817293.12
     754.89
     340.40
   78701.28
23074813.74
 6431782.00
    4832.93
;
run;

/* Output */
        num
1    $1.0 M	
2  $817.2 K	
3   $754.89	
4   $340.40	
5   $78.7 K	
6    $2.3 M	
7    $0.6 M	
8    $4.8 K

The dollar values above are now formatted much cleaner.