Problem Note 4316: WHERE returns incorrect number of observations
Beginning in Release 8.1 TSLEVEL 1M0, a WHERE statement or data set
option which is optimized with an index may return too few observations.
For the problem to occur, the following conditions must all be true:
1) the data set must be sorted by a method that sets the sort
information to VALIDATED=YES,
2) the data set must have an index created on some variable other
than the major sort key (for a composite index, the first
variable in the list must be different than the major sort key),
and,
3) the WHERE clause must contain an OR or IN operator which tests
values for the major sort key.
The following example illustrates the problem:
DATA A;
DO I=1 TO 10;
DO J=1 TO 10;
OUTPUT;
END;
END;
RUN;
PROC SORT DATA=A OUT=B (INDEX=(I));
BY J;
RUN;
DATA _NULL_;
SET B;
WHERE J IN (2,3) AND I>0;
RUN;
The second DATA step incorrectly returns only 2 observations.
To prevent the problem, sites should specify the following option in a
system CONFIG file:
debug= "yoew_opt=1";
This problem is corrected in SAS 9.
A Technical Support hot fix for Release 8.2 TSLEVEL TS2M0 for this
issue is available at:
http://www.sas.com/techsup/download/hotfix/82_sbcs_prod_list.html#004316
For customers running SAS with Asian Language Support (DBCS), this
hot fix should be downloaded from:
http://www.sas.com/techsup/download/hotfix/82_dbcs_prod_list.html#004316
Operating System and Release Information
| SAS System | Base SAS | HP-UX | 8.1 TS1M0 | 9 TS M0 |
| OpenVMS VAX | 8.1 TS1M0 | 9 TS M0 |
| OS/2 | 8.1 TS1M0 | |
| Microsoft Windows 95/98 | 8.1 TS1M0 | |
| Microsoft Windows 2000 Advanced Server | 8.1 TS1M0 | 9 TS M0 |
| Microsoft Windows 2000 Datacenter Server | 8.1 TS1M0 | 9 TS M0 |
| Microsoft Windows 2000 Server | 8.1 TS1M0 | 9 TS M0 |
| Microsoft Windows 2000 Professional | 8.1 TS1M0 | 9 TS M0 |
| Microsoft Windows NT Workstation | 8.1 TS1M0 | 9 TS M0 |
| Microsoft Windows XP Professional | 8.1 TS1M0 | 9 TS M0 |
| Windows Millennium Edition (Me) | 8.1 TS1M0 | |
| ABI+ for Intel Architecture | 8.1 TS1M0 | 9 TS M0 |
| AIX | 8.1 TS1M0 | 9 TS M0 |
| Solaris | 8.1 TS1M0 | 9 TS M0 |
| Tru64 UNIX | 8.1 TS1M0 | 9 TS M0 |
| OpenVMS Alpha | 8.1 TS1M0 | 9 TS M0 |
| IRIX | 8.1 TS1M0 | 9 TS M0 |
| z/OS | 8.1 TS1M0 | 9 TS M0 |
*
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: | 2009-05-26 15:01:46 |
| Date Created: | 2001-02-14 09:29:27 |