SUPPORT / SAMPLES & SAS NOTES
Problem Note 15400: Incorrect number of observations returned using compound WHERE statement with IN operator
 |  |  |  |  |
Using a compound WHERE clause and the IN operator referencing the same variable returns an incorrect number of observations. For example,
data one;
do x = "a", "b", "m";
output;
end;
run;
data two;
set one;
where x = "a" or x = "b" or x in ("c" "d" "e" "f"
"g" "h" "i" "j" "k" "l" "m" "n" "o" "p" "q" "r");
run;
returns 2 observations instead of 3 observations.
The circumvention is to use a WHERE clause with the IN operator that enumerates all the values, instead of a compound WHERE. If you change the above WHERE clause to the following:
where x in ("a" "b" "c" "d" "e" "f"
"g" "h" "i" "j" "k" "l" "m" "n" "o" "p" "q" "r");
the correct number of observations are returned.
Another circumvention is to use an IF statement instead of a WHERE.
Select the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| Product Family | Product | System | SAS Release | |
| Reported | Fixed* | |||
| SAS System | Base SAS | Microsoft Windows XP 64-bit Edition | 9.1 TS1M3 | 9.1 TS1M3 SP2 |
| Microsoft Windows NT Workstation | 9.1 TS1M3 | 9.1 TS1M3 SP2 | ||
| Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.1 TS1M3 | 9.1 TS1M3 SP2 | ||
| Microsoft Windows Server 2003 Enterprise Edition | 9.1 TS1M3 | 9.1 TS1M3 SP2 | ||
| Microsoft Windows Server 2003 Standard Edition | 9.1 TS1M3 | 9.1 TS1M3 SP2 | ||
| Microsoft Windows Server 2003 Datacenter Edition | 9.1 TS1M3 | 9.1 TS1M3 SP2 | ||
| Microsoft Windows 2000 Server | 9.1 TS1M3 | 9.1 TS1M3 SP2 | ||
| Microsoft Windows 2000 Professional | 9.1 TS1M3 | 9.1 TS1M3 SP2 | ||
| Microsoft Windows 2000 Advanced Server | 9.1 TS1M3 | 9.1 TS1M3 SP2 | ||
| Microsoft Windows 2000 Datacenter Server | 9.1 TS1M3 | 9.1 TS1M3 SP2 | ||
| Linux | 9.1 TS1M3 | 9.1 TS1M3 SP2 | ||
| 64-bit Enabled Solaris | 9.1 TS1M3 | 9.1 TS1M3 SP2 | ||
| z/OS | 9.1 TS1M3 | 9.1 TS1M3 SP2 | ||
| Linux on Itanium | 9.1 TS1M3 | 9.1 TS1M3 SP2 | ||
| HP-UX IPF | 9.1 TS1M3 | 9.1 TS1M3 SP2 | ||
| 64-bit Enabled HP-UX | 9.1 TS1M3 | 9.1 TS1M3 SP2 | ||
| OpenVMS Alpha | 9.1 TS1M3 | 9.1 TS1M3 SP2 | ||
| 64-bit Enabled AIX | 9.1 TS1M3 | 9.1 TS1M3 SP2 | ||
| Microsoft Windows XP Professional | 9.1 TS1M3 | 9.1 TS1M3 SP2 | ||
| Tru64 UNIX | 9.1 TS1M3 | 9.1 TS1M3 SP2 | ||