Quartz | Level 8

## Unable to get correct precision in conversion from character to numeric

In the below code the 1st 4 values, read in, equal the 5th value, but after conversion from character to numeric the comparision statement is always false. I have read several sites via Google, but have been unable to determine what I am doing wrong. If I put a ROUND function on the SUM statement (to .00001), the values are considered equal. I would like to use the values in subsequent processing and not have to use the ROUND function to check my computations. I am really interested in understandning this problem, thanks for your help.

``````data HAVE;
Format a a_num b b_num c c_num d d_num TOTAL TOTAL_num;
Length a b c d TOTAL \$100;
infile datalines delimiter="~" dsd;
input a \$ b \$ c \$ d \$ TOTAL \$;

a_num=input(a,comma18.);
b_num=input(b,comma18.);
c_num=input(c,comma18.);
d_num=input(d,comma18.);
TOTAL_num=input(TOTAL,comma18.);

datalines;
1,567,378.77~953,466.02~50,949.03~532.82~2,572,326.64
15,884,327.48~7,330,140.68~559,032.01~1,743.28~23,775,243.45
165,119.70~44,705.58~~~209,825.28
;

data WANT;
Format CHECK;
Set HAVE;

SUM_TOTAL=sum(a_num,b_num,c_num,d_num);
If sum_total^=TOTAL_num then CHECK="X";
Format a_num b_num c_num d_num TOTAL_num SUM_TOTAL comma18.2;
Run;``````

3 REPLIES 3
Opal | Level 21

## Re: Unable to get correct precision in conversion from character to numeric

Welcome to the world of numeric precision. For a detail explanation take a look at: http://support.sas.com/documentation/cdl/en/lrcon/69852/HTML/default/viewer.htm#p0ji1unv6thm0dn1gp4t...

Bottom line, use the round function.

Art, CEO, AnalystFinder.com

PROC Star

## Re: Unable to get correct precision in conversion from character to numeric

And just to add to @art297's comment, this is NOT a sas problem.  Any computer language that treats the values you input as "double-precision" (i.e. most languages) would have the same behavior.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Barite | Level 11

## Re: Unable to get correct precision in conversion from character to numeric

``````Making dollars and cents total exactly

inspired by
https://goo.gl/QijwIC
https://communities.sas.com/t5/Base-SAS-Programming/Unable-to-get-correct-precision-in-conversion-from-character-to/m-p/341321

Great question and very nice sample data. Thanks!!!

When working with dollars and cents and totals less than the national debt.
You can convert your input to numbers that have an exact representation
in a binay float. All you sums will be exact.

HAVE
====

Up to 40 obs WORK.HAVE total obs=3

Obs          A               B              C            D            TOTAL

1     1,567,378.77     953,466.02      50,949.03     532.82      2,572,326.64
2     15,884,327.48    7,330,140.68    559,032.01    1,743.28    23,775,243.45
3     165,119.70       44,705.58                                 209,825.28

WANT  (Sum total to be exactly = to sum(a,b,c,d)

Obs          A               B              C            D            TOTAL          SUM_TOTAL

1     1,567,378.77     953,466.02      50,949.03     532.82      2,572,326.64  =    2572326.64
2     15,884,327.48    7,330,140.68    559,032.01    1,743.28    23,775,243.45 =   23775243.45
3     165,119.70       44,705.58                                 209,825.28    =     209825.28

WORKING CODE
============

a_num=100*input(a,comma18.);
b_num=100*input(b,comma18.);
c_num=100*input(c,comma18.);
d_num=100*input(d,comma18.);
TOTAL_num=100*input(TOTAL,comma18.);

FULL SOLUTION
=============

data HAVE;
Length a b c d TOTAL \$100;
infile datalines delimiter="~" dsd;
input a \$ b \$ c \$ d \$ TOTAL \$;

a_num=100*input(a,comma18.);
b_num=100*input(b,comma18.);
c_num=100*input(c,comma18.);
d_num=100*input(d,comma18.);
TOTAL_num=100*input(TOTAL,comma18.);
keep
a_num
b_num
c_num
d_num
total
TOTAL_num;
cards4;
1,567,378.77~953,466.02~50,949.03~532.82~2,572,326.64
15,884,327.48~7,330,140.68~559,032.01~1,743.28~23,775,243.45
165,119.70~44,705.58~~~209,825.28
;;;;
run;quit;

data WANT;
Set HAVE;

SUM_TOTAL=sum(a_num,b_num,c_num,d_num)/100;
If sum_total^=TOTAL_num then CHECK="X";
Format a_num b_num c_num d_num TOTAL_num SUM_TOTAL comma18.2;
Run;
``````
Discussion stats
• 3 replies
• 1032 views
• 0 likes
• 4 in conversation