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
| 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 |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
| Type: | Problem Note |
| Priority: | high |
| Date Modified: | 2005-10-19 07:10:04 |
| Date Created: | 2005-05-27 15:48:16 |
