Problem Note 66214: PROC SQL queries might generate exceptions if you use the CORRESPONDING (CORR) keyword with the EXCEPT set operator to combine tables
SQL procedure queries that combine data sets using the EXCEPT set operator in conjunction with the CORRESPONDING (CORR) keyword generate exceptions. The same code does not generate exceptions in SAS® 9.4M5 (TS1M5) and earlier. See the Full Code tab for example code that triggers this issue.
If you set the SORTEDBY flag on one of the data sets, the exception does not occur. Also, if you use the ALL keyword in a query along with the CORR keyword, the exception does not occur.
Click the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
SAS System | Base SAS | z/OS | 9.4_M6 | | 9.4 TS1M6 | |
z/OS 64-bit | 9.4_M6 | | 9.4 TS1M6 | |
Microsoft® Windows® for x64 | 9.4_M6 | | 9.4 TS1M6 | |
Microsoft Windows 8 Enterprise 32-bit | 9.4_M6 | | 9.4 TS1M6 | |
Microsoft Windows 8 Enterprise x64 | 9.4_M6 | | 9.4 TS1M6 | |
Microsoft Windows 8 Pro 32-bit | 9.4_M6 | | 9.4 TS1M6 | |
Microsoft Windows 8 Pro x64 | 9.4_M6 | | 9.4 TS1M6 | |
Microsoft Windows 8.1 Enterprise 32-bit | 9.4_M6 | | 9.4 TS1M6 | |
Microsoft Windows 8.1 Enterprise x64 | 9.4_M6 | | 9.4 TS1M6 | |
Microsoft Windows 8.1 Pro 32-bit | 9.4_M6 | | 9.4 TS1M6 | |
Microsoft Windows 8.1 Pro x64 | 9.4_M6 | | 9.4 TS1M6 | |
Microsoft Windows 10 | 9.4_M6 | | 9.4 TS1M6 | |
Microsoft Windows Server 2012 Datacenter | 9.4_M6 | | 9.4 TS1M6 | |
Microsoft Windows Server 2012 R2 Datacenter | 9.4_M6 | | 9.4 TS1M6 | |
Microsoft Windows Server 2012 R2 Std | 9.4_M6 | | 9.4 TS1M6 | |
Microsoft Windows Server 2012 Std | 9.4_M6 | | 9.4 TS1M6 | |
Microsoft Windows Server 2016 | 9.4_M6 | | 9.4 TS1M6 | |
Microsoft Windows Server 2019 | 9.4_M6 | | 9.4 TS1M6 | |
Windows 7 Enterprise 32 bit | 9.4_M6 | | 9.4 TS1M6 | |
Windows 7 Enterprise x64 | 9.4_M6 | | 9.4 TS1M6 | |
Windows 7 Home Premium 32 bit | 9.4_M6 | | 9.4 TS1M6 | |
Windows 7 Home Premium x64 | 9.4_M6 | | 9.4 TS1M6 | |
Windows 7 Professional 32 bit | 9.4_M6 | | 9.4 TS1M6 | |
Windows 7 Professional x64 | 9.4_M6 | | 9.4 TS1M6 | |
Windows 7 Ultimate 32 bit | 9.4_M6 | | 9.4 TS1M6 | |
Windows 7 Ultimate x64 | 9.4_M6 | | 9.4 TS1M6 | |
64-bit Enabled AIX | 9.4_M6 | | 9.4 TS1M6 | |
64-bit Enabled Solaris | 9.4_M6 | | 9.4 TS1M6 | |
HP-UX IPF | 9.4_M6 | | 9.4 TS1M6 | |
Linux for x64 | 9.4_M6 | | 9.4 TS1M6 | |
Solaris for x64 | 9.4_M6 | | 9.4 TS1M6 | |
*
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.
This is code that can be used to replicate the problem.
/*Code that generates the exception*/
data a;
do x=1 to 30 by 3;
a=x+2;
output;
end;
run;
proc sql;
create table a2 as
select * from a
union corr
select * from a;
quit;
data b;
do x=1 to 30 by 2;
b=x+2;
output;
end;
run;
proc sql;
create table c1 as
select * from a2
except corr
select * from b;
quit;
/*Code that shows added the SORTEDBY flag prevents the exception*/
data a;
do x=1 to 30 by 3;
a=x+2;
output;
end;
run;
proc sql;
create table a2 (sortedby=_null_) as
select * from a
union corr
select * from a;
quit;
data b;
do x=1 to 30 by 2;
b=x+2;
output;
end;
run;
proc sql;
create table c2 as
select * from a2
except corr
select * from b;
quit;
/*Code that shows that adding the ALL keyword also prevents the exception*/
data a;
do x=1 to 30 by 3;
a=x+2;
output;
end;
run;
proc sql;
create table a2 as
select * from a
union corr
select * from a;
quit;
data b;
do x=1 to 30 by 2;
b=x+2;
output;
end;
run;
proc sql;
create table c3 as
select * from a2
except corr all
select * from b;
quit;
Type: | Problem Note |
Priority: | high |
Date Modified: | 2020-10-01 16:47:32 |
Date Created: | 2020-06-29 11:09:55 |