BookmarkSubscribeRSS Feed
Josh
Calcite | Level 5
Apologies if there is already a thread on this but can anyone point me to a sample picture or other format that will display cents with a decimal point and two digits to the right of it, i.e. as if the quantity is dollars and cents. Dividing by 100 introduces rounding errors which add up so that PROC COMPARE lists half my records with differences of the order or 10E-07. Alternatively can one specify a FUZZ type of option in that PROC to deem differences less that 5*10E-03 (say) to be taken as zero?
4 REPLIES 4
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Have you considered using the DOLLAR format with this type of syntax:

1 data _null_;
2 x = 200.22;
3 put x= dollar10.2;
4 run;

x=$200.22
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

Otherwise suggest sharing your SAS program code (from a SAS-generated log) and an input/output data sample to help clarify your situation / requirement.

Scott Barry
SBBWorks, Inc.
PaulHomes
Rhodochrosite | Level 12
Hi Josh,

I know what you mean. When currency amounts are stored as dollar values with fractional cents when a large number of records are aggregated the total can be incorrect. This stems from the fact that 1 cent as .01 cannot be represented accurately in binary (just like 1/3 cant be represented accurately in decimal .333333 recurring). As you suggested a good way to handle this is to store the currency value as an integer number of cents and then format it as a dollar value by dividing by 100 to display it.

The following code shows a sample dollarc format that formats a integer cents value as a dollar and cents amount. This particular sample puts a negative amount in brackets. If you don't want that its easy enough to modify the format.

Here's the sample code:

* a format that displays a cents integer value as a dollar value and negative values in parentheses;
proc format;
picture dollarc
low - <0 = '00,000,000,000,009.99)' (mult=1 prefix='($')
0 - high = '00,000,000,000,009.99 ' (mult=1 prefix='$')
;
run;

data demo;
attrib cost label='Cost' format=dollarc.;
infile cards;
input cost;
cards;
-1234567898
-1234567
-12345
-123
-99
-1
0
1
99
123
12345
1234567
1234567898
;
run;

proc print data=demo label;
run;

... and here's the sample output (it will line up properly when you run it, its just that the discussion forum doesn't let me have preformatted text as far as I know):

Obs Cost

1 ($12,345,678.98)
2 ($12,345.67)
3 ($123.45)
4 ($1.23)
5 ($0.99)
6 ($0.01)
7 $0.00
8 $0.01
9 $0.99
10 $1.23
11 $123.45
12 $12,345.67
13 $12,345,678.98

Cheers
Paul
http://platformadmin.com/
Josh
Calcite | Level 5
That's it. I've never used picture formats but figured they'd be the thing to use as an edit mask somewhat along the lines of 9V99 in COBOL. Thanks.
Peter_C
Rhodochrosite | Level 12
consider using the ROUND option on the picture statement

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 6560 views
  • 0 likes
  • 4 in conversation