49175 - Incorrect sort information might be placed in the header of a SAS® data set that was created by the SQL procedure

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
Product Family	Product	System	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

* 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:	alert

Date Modified:	2013-02-21 18:29:59
Date Created:	2013-02-15 10:21:23

Support

Problem Note 49175: Incorrect sort information might be placed in the header of a SAS® data set that was created by the SQL procedure

Operating System and Release Information