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:

• SELECT DISTINCT
• GROUP BY that includes a subset of the variables that are part of the DISTINCT and the order of the variables differ

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.

Operating System and Release Information

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:

A fix for this issue for Base SAS 9.3_M2 is available at: