SUPPORT / SAMPLES & SAS NOTES
Problem Note 49175: Incorrect sort information might be placed in the header of a SAS® data set that was created by the SQL procedure
 |  |  |  |  |
Incorrect results might occur when you use a SAS data set that was created by the SQL procedure. This problem occurs when the sorted by information in the header of the SAS data set is incorrect.
Any code in which the invalid information is used to generate results might generate wrong results because the SAS data set is not really sorted by the variables that are listed. The incorrect information might occur when DISTINCT is used in an inline view, and, outside the inline view, the data is grouped by the variables in a different order.
The following code illustrates one case that can produce the issue:
proc sql;
create table test as
select sex, age,
case when flag_sum = 1 then 1 else 0 end as match
from
(select sex, age, sum(flag) as flag_sum
from
(select age, sex,
case when name in('Alfred','Alice','Carol','Mary')
then 1 else 0 end as flag
from
(select distinct age,sex,name
from sashelp.class))
group by sex,age)
;
quit;
The only workaround is to split the query into multiple steps rather than using inline views.
Operating System and Release Information
| Product Family | Product | System | SAS Release | |
| Reported | Fixed* | |||
| SAS System | Base SAS | z/OS | 9.2 TS1M0 | 9.4 TS1M0 |
| Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.2 TS1M0 | 9.4 TS1M0 | ||
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.2 TS1M0 | 9.4 TS1M0 | ||
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.2 TS1M0 | 9.4 TS1M0 | ||
| Microsoft Windows XP 64-bit Edition | 9.2 TS1M0 | 9.4 TS1M0 | ||
| Microsoft® Windows® for x64 | 9.2 TS1M0 | 9.4 TS1M0 | ||
| Linux | 9.2 TS1M0 | |||
| HP-UX IPF | 9.2 TS1M0 | |||
| Microsoft Windows Server 2003 Standard Edition | 9.2 TS1M0 | |||
| Microsoft Windows XP Professional | 9.2 TS1M0 | 9.4 TS1M0 | ||
| Windows Vista | 9.2 TS1M0 | 9.4 TS1M0 | ||
| Windows Vista for x64 | 9.2 TS1M0 | 9.4 TS1M0 | ||
| 64-bit Enabled AIX | 9.2 TS1M0 | 9.4 TS1M0 | ||
| 64-bit Enabled HP-UX | 9.2 TS1M0 | 9.4 TS1M0 | ||
| 64-bit Enabled Solaris | 9.2 TS1M0 | 9.4 TS1M0 | ||
| Linux for x64 | 9.2 TS1M0 | 9.4 TS1M0 | ||
| OpenVMS on HP Integrity | 9.2 TS1M0 | 9.4 TS1M0 | ||
| Solaris for x64 | 9.2 TS1M0 | 9.4 TS1M0 | ||
| Microsoft Windows Server 2003 Enterprise Edition | 9.2 TS1M0 | |||
| Microsoft Windows Server 2003 for x64 | 9.2 TS2M0 | |||
| Microsoft Windows Server 2008 for x64 | 9.2 TS2M0 | 9.4 TS1M0 | ||
| Microsoft Windows Server 2003 Datacenter Edition | 9.2 TS1M0 | |||