A WHERE clause that contains an AND condition returns incorrect results when the first two arguments of the AND condition are the same


If you use a WHERE clause that contains an AND condition and the first two arguments in the AND condition are identical, an incorrect number of observations might be returned.

The following code example illustrates the issue. After the code executes, multiple observations occur, but the expectation is that one observation should be returned:

data one;
  infile datalines truncover;
  input x1 $ x2 $;
datalines;
house
house car
house house
;
run;
data two;
  set one;
  where indexw("house", x1) and indexw("house", x2);
run;

No notes, warnings, or errors are issued in the SAS® log to indicate that an incorrect number of observations has been returned.

Workarounds

There are two workarounds.

The first workaround is to change the WHERE clause to have the following syntax:

where 0 < indexw("house", x1) and 0 < indexw("house", x2);

After you make that change, one observation is returned, as expected.

The second workaround is to substitute an IF statement in place of the WHERE clause. Here is an example:

data two;
  set one;
  if indexw("house", x1) and indexw("house", x2);
run;

Using an IF statement also returns the correct number of observations, which is one in this scenario.