Problem Note 57899: PROC SQL returns incorrect results reading from an In-Line View or an SQL View
 |  |  |  |  |  |  |
The SQL procedure returns incorrect results from an In-Line View or an SQL View. The incorrect results occur when the following conditions are true:
- The view contains a column(s) created using a summary function
- A new column is created from the summarized column(s) created in the view
- A WHERE clause contains a Calculated reference to the column(s) created in item 2
To see an example of code that produces the incorrect and correct results, click the Full Code tab. Click the Output tab to see the incorrect and correct results.
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 | z/OS | 9.3 TS1M2 | 9.4 TS1M3 |
| z/OS 64-bit | 9.3 TS1M2 | 9.4 TS1M3 | ||
| Microsoft® Windows® for x64 | 9.3 TS1M2 | 9.4 TS1M3 | ||
| Microsoft Windows 8 Enterprise 32-bit | 9.3 TS1M2 | 9.4 TS1M3 | ||
| Microsoft Windows 8 Enterprise x64 | 9.3 TS1M2 | 9.4 TS1M3 | ||
| Microsoft Windows 8 Pro 32-bit | 9.3 TS1M2 | 9.4 TS1M3 | ||
| Microsoft Windows 8 Pro x64 | 9.3 TS1M2 | 9.4 TS1M3 | ||
| Microsoft Windows 8.1 Enterprise 32-bit | 9.3 TS1M2 | 9.4 TS1M3 | ||
| Microsoft Windows 8.1 Enterprise x64 | 9.3 TS1M2 | 9.4 TS1M3 | ||
| Microsoft Windows 8.1 Pro 32-bit | 9.3 TS1M2 | 9.4 TS1M3 | ||
| Microsoft Windows 8.1 Pro x64 | 9.3 TS1M2 | 9.4 TS1M3 | ||
| Microsoft Windows Server 2008 | 9.3 TS1M2 | 9.4 TS1M3 | ||
| Microsoft Windows Server 2008 R2 | 9.3 TS1M2 | 9.4 TS1M3 | ||
| Microsoft Windows Server 2008 for x64 | 9.3 TS1M2 | 9.4 TS1M3 | ||
| Microsoft Windows Server 2012 Datacenter | 9.3 TS1M2 | 9.4 TS1M3 | ||
| Microsoft Windows Server 2012 R2 Datacenter | 9.3 TS1M2 | 9.4 TS1M3 | ||
| Microsoft Windows Server 2012 R2 Std | 9.3 TS1M2 | 9.4 TS1M3 | ||
| Microsoft Windows Server 2012 Std | 9.3 TS1M2 | 9.4 TS1M3 | ||
| Windows 7 Professional 32 bit | 9.3 TS1M2 | 9.4 TS1M3 | ||
| Windows 7 Professional x64 | 9.3 TS1M2 | 9.4 TS1M3 | ||
| 64-bit Enabled AIX | 9.3 TS1M2 | 9.4 TS1M3 | ||
| 64-bit Enabled HP-UX | 9.3 TS1M2 | 9.4 TS1M3 | ||
| 64-bit Enabled Solaris | 9.3 TS1M2 | 9.4 TS1M3 | ||
| HP-UX IPF | 9.3 TS1M2 | 9.4 TS1M3 | ||
| Linux | 9.3 TS1M2 | 9.4 TS1M3 | ||
| Linux for x64 | 9.3 TS1M2 | 9.4 TS1M3 | ||
| Solaris for x64 | 9.3 TS1M2 | 9.4 TS1M3 | ||
Examples 1 and 2 illustrate the incorrect results that occur when using an SQL view or an Inline view and a WHERE clause that references a Calculated column.
Example 3 returns the correct results.
title;
data example;
class='a'; cnt1=5; cnt2=0; output;
class='b'; cnt1=23; cnt2=3; output;
class='b'; cnt1=23; cnt2=3; output;
class='c'; cnt1=37; cnt2=4; output;
run;
title '** incorrect results when an Inline View is used **';
proc sql;
create table incorrect1 as
select
class
,cnt1
,cnt2
,(cnt1 + cnt2) as tot
from
( select
class
,sum(cnt1) as cnt1
,sum(cnt2) as cnt2
from example
group by class )
where calculated tot>0 ;
quit;
proc print data=incorrect1;
run;
title '** incorrect results occur when an SQL view is used **';
proc sql;
create view temp as
select
class
,sum(cnt1) as cnt11
,sum(cnt2) as cnt22
from example
group by class;
create table incorrect2 as
select
class
,cnt11
,cnt22
,(cnt11 + cnt22) as tot
from temp
where calculated tot > 0
;
quit;
proc print data=incorrect2;
run;
title '** correct results occur when a view is NOT used **';
proc sql;
create table correct3 as
select
class
,sum(cnt1) as cnt1
,sum(cnt2) as cnt2
,(calculated cnt1 + calculated cnt2) as tot
from example
group by class
having calculated tot>0
;
quit;
proc print data=correct3;
run;
title;
** incorrect results when an Inline View is used ** Obs class cnt1 cnt2 tot 1 b 46 6 5 2 c 37 4 52 --------------------------------------------------------- ** incorrect results occur when an SQL view is used ** Obs class cnt11 cnt22 tot 1 b 46 6 5 2 c 37 4 52 --------------------------------------------------------- ** correct results occur when a view is NOT used ** Obs class cnt1 cnt2 tot 1 a 5 0 5 2 b 46 6 52 3 c 37 4 41
A fix for this issue for Base SAS 9.4_M3 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/V01.html#57899A fix for this issue for Base SAS 9.3_M2 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/I22.html#57899| Type: | Problem Note |
| Priority: | alert |
| Date Modified: | 2016-04-04 11:09:01 |
| Date Created: | 2016-03-18 15:18:27 |