Compare 2 numeric variable using <, but not working.

[CHEESE123]

Anyone can help on below query? please...

proc sql;
create table Result1 as
select * , 0.75*Numeric1 as Numeric2 format 12.4
from DATA1 where ABC=0 and CBD<=25
and calculated Numeric2 < Numeric3;
quit;

Outcome of Result1:

Numeric2  Numeric3;

1111           1111 (Not sure why this also appear in my output)

1000           1001

[Kurt_Bremser · Accepted solution]

This is an artifact caused by the way the computer stores and calculates numbers (binary in 8 bytes, floating point). Whenever decimal fractions are converted to binary, there is a fair chance that the decimal fraction cannot be represented exactly in binary and causes a minimal error of about E-14 (with a number like 1111 that would be about 1E-11).

Your display format limits the decimals to 4 digits, so you can't see that difference.

Use the round() function to get rid of the artifacts:

proc sql;
create table Result1 as
  select
    *,
    round(0.75*Numeric1,.0001) as Numeric2 format 12.4
  from DATA1
  where ABC=0 and CBD<=25
    and calculated Numeric2 < round(Numeric3,0001)
;
quit;

For more detailed information about the issue, google "sas numeric precision".

[CHEESE123]

Thanks!!! Now it is working correctly 🙂