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 | ||
A fix for SAS 9.1.3 (9.1 TS1M3) for this issue is available at:
http://www.sas.com/techsup/download/hotfix/e9_sbcs_prod_list.html#015400A fix for SAS 9.1.3 (9.1 TS1M3) with Asian Language Support (DBCS) for this issue is available at :
http://www.sas.com/techsup/download/hotfix/e9_dbcs_prod_list.html#015400| Type: | Problem Note |
| Priority: | high |
| Date Modified: | 2005-10-19 07:10:04 |
| Date Created: | 2005-05-27 15:48:16 |