Unable to get correct precision in conversion from character to numeric

[Jay_TxOAG]

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;

[art297]

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

[mkeintz]

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.

[rogerjdeangelis]

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;