Incorrect SORTED BY information might be stored in the header of a SAS data set. Subsequent queries that use the incorrect information might result in incorrect results. This issue occurs when the following conditions exist in the SQL procedure query:
The SORTED BY information is set to match what is listed in the GROUP BY clause, whereas the actual sort order of the data is based on the order of the variables in the SELECT statement.
The only workaround is to break the query into multiple steps.
Product Family | Product | System | SAS Release | |
Reported | Fixed* | |||
SAS System | Base SAS | z/OS | 9.2 TS2M3 | 9.4 TS1M0 |
Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.2 TS2M3 | |||
Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.2 TS2M3 | |||
Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.2 TS2M3 | |||
Microsoft Windows XP 64-bit Edition | 9.2 TS2M3 | |||
Microsoft® Windows® for x64 | 9.2 TS2M3 | 9.4 TS1M0 | ||
Microsoft Windows Server 2003 Datacenter Edition | 9.2 TS2M3 | |||
Microsoft Windows Server 2003 Enterprise Edition | 9.2 TS2M3 | |||
Microsoft Windows Server 2003 Standard Edition | 9.2 TS2M3 | |||
Microsoft Windows Server 2003 for x64 | 9.2 TS2M3 | |||
Microsoft Windows Server 2008 | 9.2 TS2M3 | |||
Microsoft Windows Server 2008 R2 | 9.2 TS2M3 | 9.4 TS1M0 | ||
Microsoft Windows Server 2008 for x64 | 9.2 TS2M3 | 9.4 TS1M0 | ||
Microsoft Windows XP Professional | 9.2 TS2M3 | |||
Windows 7 Enterprise 32 bit | 9.2 TS2M3 | |||
Windows 7 Enterprise x64 | 9.2 TS2M3 | 9.4 TS1M0 | ||
Windows 7 Home Premium 32 bit | 9.2 TS2M3 | |||
Windows 7 Home Premium x64 | 9.2 TS2M3 | |||
Windows 7 Professional 32 bit | 9.2 TS2M3 | |||
Windows 7 Professional x64 | 9.2 TS2M3 | 9.4 TS1M0 | ||
Windows 7 Ultimate 32 bit | 9.2 TS2M3 | |||
Windows 7 Ultimate x64 | 9.2 TS2M3 | |||
Windows Vista | 9.2 TS2M3 | |||
Windows Vista for x64 | 9.2 TS2M3 | |||
64-bit Enabled AIX | 9.2 TS2M3 | 9.4 TS1M0 | ||
64-bit Enabled HP-UX | 9.2 TS2M3 | 9.4 TS1M0 | ||
64-bit Enabled Solaris | 9.2 TS2M3 | 9.4 TS1M0 | ||
HP-UX IPF | 9.2 TS2M3 | 9.4 TS1M0 | ||
Linux | 9.2 TS2M3 | 9.4 TS1M0 | ||
Linux for x64 | 9.2 TS2M3 | 9.4 TS1M0 | ||
OpenVMS on HP Integrity | 9.2 TS2M3 | 9.4 TS1M0 | ||
Solaris for x64 | 9.2 TS2M3 | 9.4 TS1M0 |
data ews;
input acct_num actn_id tran_cd $;
cards;
1234 1 c1
1234 1 b3
1234 1 c2
2586 1 c1
2586 1 b3
2586 1 b3
2586 1 c2
8646 1 c1
8645 1 b3
8646 1 b3
8646 1 c2
8938 1 c1
8938 1 b3
8938 1 b3
6792 2 c1
6792 2 c2
9572 2 c1
9572 2 c2
8948 2 c1
8948 2 b3
8948 2 b3
8948 2 c2
7532 2 c1
7532 2 b3
7532 2 b3
8521 2 c1
8521 2 c2
5678 2 c1
3256 2 c1
3256 2 b3
3256 2 b3
1478 2 c1
1478 2 b3
1478 2 b3
1478 2 c2
;
run;
/* this table will be listed as being sorted by actn_id acct_num when in fact it is really
sorted by acct_num actn_id */
proc sql;
create table dup as
(select actn_id, acct_num,
case when tran_sum = 3 then 1 else 0 end as reviewed,
case when tran_sum < 3 then 1 else 0 end as non_reviewed
from
(select actn_id, acct_num, sum(tran_flag) as tran_sum
from
(select acct_num, actn_id,
case when tran_cd in ('C1','B3','C2')
then 1 else 0 end as tran_flag
from
(select distinct acct_num, actn_id, tran_cd from ews))
Group by actn_id, acct_num))
;
quit;
/*output shows it is sorted by ACCT_NUM, ACTN_ID but PROC CONTENTS says its the opposite
non_
Obs actn_id acct_num reviewed reviewed
1 1 1234 0 1
2 2 1478 0 1
3 1 2586 0 1
4 2 3256 0 1
5 2 5678 0 1
6 2 6792 0 1
7 2 7532 0 1
8 2 8521 0 1
9 1 8645 0 1
10 1 8646 0 1
11 1 8938 0 1
12 2 8948 0 1
13 2 9572 0 1
Sort Information
Sortedby actn_id acct_num
Validated YES
Character Set EBCDIC
Sort Option NODUPKEY
*/
/* Doing a subsetting WHERE on ACTN_ID returns the wrong result */
proc print data=dup;
where actn_id=1;
run;
/* Output showing wrong results
non_
Obs actn_id acct_num reviewed reviewed
1 1 1234 0 1
*/
A fix for this issue for Base SAS 9.21_M3 is available at:
http://ftp.sas.com/techsup/download/hotfix/HF2/B25.html#50784A fix for this issue for Base SAS 9.3_M2 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/I22.html#50784Type: | Problem Note |
Priority: | alert |
Date Modified: | 2018-07-10 11:14:23 |
Date Created: | 2013-08-19 09:25:16 |