question about comparison operators

[sy2426]

Hi,

I have a question about the NOT EQUAL operator and hope you can help. I want to compare the variable i created ("hours3") against the original data "hours1" and "hours2". Below is my code.

data check1;
set example;
where hours1^=hours3 or hours2^=hours3;
keep id hours1 hours2 hours3 ;
run;

However, SAS ouput lots of observations that the three variables values are actually EQUAL.

id	hours1	hours2	hours3
1	254.4	254.4	254.4
2	243.2	243.2	243.2
3	290.9	290.9	290.9
4	291.9	291.9	291.9
5	202.2	202.2	202.2

I also made sure the format of three variables are the same but SAS still output values that are actually EQUAL. May I know what is the problem? Thanks!

[ballardw]

You show some values with one decimal point displayed. What is the FORMAT currently assigned to the values? By default the displayed values are actually rounded by the format and so an actual value of 254.41 and 254.400338 would both display as 254.4.

See if this yields more of what you expect. The round function will round the values to the tenths decimal before the comparisons.

data check1;
   set example;
   where round(hours1, 0.1)^= round(hours3,0.1) or round(hours2,0.1) ^= round(hours3,0.1);
   keep id hours1 hours2 hours3 ;
run;

[sy2426]

Thanks so much ballardw! your solution using the round function worked.

My original variable hours and hours2 are in BEST12 (below). 

Alphabetic List of Variables and Attributes
#	Variable	Type	Len	Format	Informat
2	hours	Num	8	BEST12.	BEST32.
3	hours2	Num	8	BEST12.	BEST32.
1	id	Num	8	BEST12.	BEST32.
4	hours3	Num	8	BEST12.	BEST12.

[Rick_SAS]

This is a specific case of the general maxim: Avoid testing floating-point values for equality

A more robust check for equality of floating-point numbers x and y is

abs(x - x) < tol
where tol is a small number such as 1e-6.

If testing floating-point numbers is something you do a lot, you could incorporate the logic into a macro, as follows:

/* gernate some data where there are small difference betwen numbers */
data A;
call streaminit(12345);
do x1 = -5 to 5;
   x2 = x1 + rand("Normal",0,1e-6);
   x3 = x2 + rand("Normal",0,1e-6);
   output;
end;
run;

%macro FloatEq(a,b,tol=1e-6);
   (abs(&a - &b) < &tol)
%mend;

proc print data=A; 
where ^%FloatEq(x1,x3) OR ^%FloatEq(x2,x3);
format _all_ 12.9;
run;