topic Re: Compare 2 numeric variable using <, but not working. in SAS Studio

Compare 2 numeric variable using <, but not working.

CHEESE123 — Thu, 30 Apr 2020 06:42:47 GMT

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

Re: Compare 2 numeric variable using <, but not working.

Kurt_Bremser — Thu, 30 Apr 2020 06:55:09 GMT

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".

Re: Compare 2 numeric variable using <, but not working.

CHEESE123 — Thu, 30 Apr 2020 07:05:37 GMT

Thanks!!! Now it is working correctly 🙂