Problem Note 65539: A query might return incorrect results if it contains an SQL JOIN to a variable that was created in an SQL view
 |  |  |  |  |
In Base SAS® software, incorrect results might occur when you run an SQL procedure query and the following conditions exist:
- You create a view that includes the creation of a new variable via a CASE expression and involves the join of two data sets.
- You join the previously created view with a third data set using the newly created variable in the join criteria as well as further subsetting the results using the newly created variable.
For example, you would encounter this issue if you ran code similar to the following:
data ds1;
infile cards dsd dlm=',';
input num_1 num_flg_1;
cards;
1,1
2,0
3,0
;
run;
infile cards dsd dlm=',';
input num_1 num_flg_1;
cards;
1,1
2,0
3,0
;
run;
data ds2;
infile cards dsd dlm=',';
input num_1 chr_1 $ chr_2 $;
cards;
1,A,X
2,B,Y
3,D,Q
;
run;
infile cards dsd dlm=',';
input num_1 chr_1 $ chr_2 $;
cards;
1,A,X
2,B,Y
3,D,Q
;
run;
data ds3;
infile cards dsd dlm=',';
input num_1 chr_1 $;
cards;
1,X
2,X
3,Y
;
run;
infile cards dsd dlm=',';
input num_1 chr_1 $;
cards;
1,X
2,X
3,Y
;
run;
proc sql;
create view v1 as
select
t1.num_1,
(case when t2.chr_1 = "X" then 1
when t2.chr_1 = "Y" then 0
else 2 end) as num_flg_1
from
ds2 t1
left join ds3 t2
on t1.num_1 = t2.num_1
;
quit;
create view v1 as
select
t1.num_1,
(case when t2.chr_1 = "X" then 1
when t2.chr_1 = "Y" then 0
else 2 end) as num_flg_1
from
ds2 t1
left join ds3 t2
on t1.num_1 = t2.num_1
;
quit;
proc sql;
create table zero_rows_result as
select
t4.num_flg_1 as tab_2_numflg
from
ds1 t3, v1 t4
where
t3.num_flg_1 = t4.num_flg_1 and
t3.num_flg_1 = 1
;
quit;
create table zero_rows_result as
select
t4.num_flg_1 as tab_2_numflg
from
ds1 t3, v1 t4
where
t3.num_flg_1 = t4.num_flg_1 and
t3.num_flg_1 = 1
;
quit;
When you run the code above, you get the incorrect result of 0 rows when it should have generated two rows.
The workaround is to create a table rather than a view in the first step and then use that table in the subsequent query.
Click the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| Product Family | Product | System | SAS Release | |
| Reported | Fixed* | |||
| SAS System | Base SAS | Microsoft Windows Server 2012 R2 Datacenter | 9.4 TS1M2 | |
| Microsoft Windows Server 2012 Datacenter | 9.4 TS1M2 | |||
| Microsoft Windows Server 2008 R2 | 9.4 TS1M2 | |||
| Microsoft Windows Server 2008 | 9.4 TS1M2 | |||
| Microsoft Windows 10 | 9.4 TS1M2 | |||
| Microsoft Windows 8.1 Pro x64 | 9.4 TS1M2 | |||
| Microsoft Windows 8.1 Pro 32-bit | 9.4 TS1M2 | |||
| Microsoft Windows 8.1 Enterprise 32-bit | 9.4 TS1M2 | |||
| Microsoft Windows 8 Pro x64 | 9.4 TS1M2 | |||
| Microsoft Windows 8 Pro 32-bit | 9.4 TS1M2 | |||
| Microsoft Windows 8 Enterprise x64 | 9.4 TS1M2 | |||
| Microsoft Windows 8 Enterprise 32-bit | 9.4 TS1M2 | |||
| z/OS 64-bit | 9.4 TS1M2 | |||
| z/OS | 9.4 TS1M2 | |||
| Microsoft Windows Server 2008 for x64 | 9.4 TS1M2 | |||
| Microsoft Windows 8.1 Enterprise x64 | 9.4 TS1M2 | |||
| Microsoft® Windows® for x64 | 9.4 TS1M2 | |||
| Microsoft Windows Server 2012 R2 Std | 9.4 TS1M2 | |||
| Microsoft Windows Server 2012 Std | 9.4 TS1M2 | |||
| Windows 7 Enterprise 32 bit | 9.4 TS1M2 | |||
| Windows 7 Enterprise x64 | 9.4 TS1M2 | |||
| Windows 7 Home Premium 32 bit | 9.4 TS1M2 | |||
| Windows 7 Home Premium x64 | 9.4 TS1M2 | |||
| Windows 7 Professional 32 bit | 9.4 TS1M2 | |||
| Windows 7 Professional x64 | 9.4 TS1M2 | |||
| Windows 7 Ultimate 32 bit | 9.4 TS1M2 | |||
| Windows 7 Ultimate x64 | 9.4 TS1M2 | |||
| 64-bit Enabled AIX | 9.4 TS1M2 | |||
| 64-bit Enabled Solaris | 9.4 TS1M2 | |||
| HP-UX IPF | 9.4 TS1M2 | |||
| Linux for x64 | 9.4 TS1M2 | |||
| Solaris for x64 | 9.4 TS1M2 | |||
A fix for this issue for Base SAS 9.4_M6 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/D9T.html#65539A fix for this issue for Base SAS 9.4_M5 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/B6Q.html#65539| Type: | Problem Note |
| Priority: | high |
| Date Modified: | 2020-02-20 10:59:09 |
| Date Created: | 2020-02-11 12:11:26 |