best practies to understand the difference between two number that appear the same

[Rakeon]

Hi,

I have two table,

one has this value: a=2894378 and the second has this value: b = 28943.78.

I make the join between them, and when i verify if they are equal :

c = a / 100

if  c = b 

sas interprets like different value.

I try to change also the format, but after there are only 0 value.

I don't understand why...

[flyover]

Maybe try the other way around.
C = b * 100
Check if c = a

[Rakeon]

I get the same problem

[Quentin · Accepted solution]

Could be a numeric precision issue.

Try checking something like: if abs(c-b) < .000001   

Then google / search lexjansen.com for SAS numeric precision.

[ballardw]

What is the actual FORMAT of the value when b=28943.78?  A format will display values as requested and may often round values. It may be the data actually has more decimals stored but you haven't examined them.

Run this program and examine the results:

data _null_;
  file print;
  do x=28943.778 to 28943.786 by 0.001;
     put "Format 10.3: " x= 10.3 " Format best8.: " x= best8.;
  end;
run;

[FreelanceReinh]

Hi @Rakeon,

I suspect that the exact value of b is not equal to 28943.78, although it might be displayed as 28943.78 with common formats such as BEST12.. Tiny deviations from the internal standard representation of a numeric value can easily result from calculations or data import from other platforms, external databases, etc.

When I compare numeric values where this might be an issue I usually apply the ROUND function with a suitable rounding unit to either or both sides of the equals sign (see code example below). Other options include checking the difference (as suggested by @Quentin) or using the COMPFUZZ function.

It's also good practice to remove those tiny errors as soon as they occur. That is, apply the ROUND function to affected variables after data import from an external database or to the result of a calculation (e.g., total = round(amount1 + amount2, 0.01) if amount1 and amount2 have up to two decimals).

On Windows (or Unix) SAS I'd expect a quotient of a 7-digit integer divided by 100 to be "clean" in the sense that it should be equal to the corresponding decimal fraction with two decimals. (Please let me know if you find a counterexample on a different platform.) However, multiplying a decimal fraction with two decimals by 100 will not be equal to the expected integer in a substantial number of cases, e.g, 0.07*100 ne 7 is true.

Example:

622   data _null_;
623   a=2894378;
624   b=28943.78;
625   c=a/100;
626   d=20000.08+8943.70;
627   e=b*100;
628   f=d*100;
629   put (a--f) (=hex16. /);
630   put (d f) (=best12. /);
631   if b=round(d,1e-9) then put 'OK';
632   diff=d-b;
633   put diff=;
634   run;

a=4146151500000000
b=40DC43F1EB851EB8
c=40DC43F1EB851EB8
d=40DC43F1EB851EB9
e=4146151500000000
f=4146151500000001
d=28943.78
f=2894378
OK
diff=3.637979E-12

The HEX16. format is ideal for diagnosing such issues. As shown in the log above, the calculated value d is slightly too large (by 2**-38=3.6...E-12) due to a rounding error, but common formats (even BEST32. or 32.26) don't reveal this error because they slightly round values. The equality e=a holds in this particular case, but could fail with similar numbers. Variable f shows the risk of not caring about "unclean" values such as d: The rounding error has propagated and now affects what should actually be an integer value (so that a ne f). Again, the seemingly clean BEST12. display is treacherous. The rounding unit 1E-9 is greater than diff and also small enough to preserve relevant decimals. So it is suitable for removing this error.