When you use PROC SQL to join multiple database management system (DBMS) tables that use the same libref, incorrect results occur. This issue occurs when all of the following are true:  

• You create a view that references a DBMS table.
• You create a second view in which the following is true:
  • The first view is referenced.
  • Two new variables are created through two different summary functions.
  • The GROUP BY clause includes all the variables in the SELECT clause that are not part of a summary function.
• You create a third view that references the first view.
• You create a fourth view in which the following is true:
  • View2 and view3 are joined.
  • A CASE expression is used to create a new variable.
  • One of the variables created by use of the summary function in the second view is renamed.
• You create a table in which the following is true:
  • The fourth view is referenced.
  • A summary function is included in the SELECT clause.
  • The GROUP BY clause contains the variables that was created by a summary function in view2 and renamed in view4 and the variable created by the case expression in view4.

Click the Full Code tab to see example code that causes this issue to occur.

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	Product Release		SAS Release
			Reported	Fixed*	Reported	Fixed*
SAS System	Base SAS	Solaris for x64	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		Linux for x64	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		Linux	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		64-bit Enabled AIX	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		Windows Vista for x64	9.3_M2		9.3 TS1M2
		Windows Vista	9.3_M2		9.3 TS1M2
		Windows 7 Ultimate x64	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		Windows 7 Ultimate 32 bit	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		Windows 7 Professional x64	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		Windows 7 Professional 32 bit	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		Microsoft Windows 8.1 Enterprise x64	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		Microsoft Windows 8.1 Enterprise 32-bit	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		Microsoft Windows 8 Pro x64	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		Microsoft Windows 8 Pro 32-bit	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		Microsoft Windows 8 Enterprise x64	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		Microsoft Windows 8 Enterprise 32-bit	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		Microsoft® Windows® for x64	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		z/OS 64-bit	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		HP-UX IPF	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		64-bit Enabled Solaris	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		64-bit Enabled HP-UX	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		Windows 7 Home Premium x64	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		Windows 7 Home Premium 32 bit	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		Windows 7 Enterprise x64	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		Windows 7 Enterprise 32 bit	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		Microsoft Windows XP Professional	9.3_M2		9.3 TS1M2
		Microsoft Windows Server 2012 Std	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		Microsoft Windows Server 2012 R2 Std	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		Microsoft Windows Server 2012 R2 Datacenter	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		Microsoft Windows Server 2012 Datacenter	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		Microsoft Windows Server 2008 for x64	9.3_M2		9.3 TS1M2
		Microsoft Windows Server 2008 R2	9.3_M2		9.3 TS1M2
		Microsoft Windows Server 2008	9.3_M2		9.3 TS1M2
		Microsoft Windows Server 2003 for x64	9.3_M2		9.3 TS1M2
		Microsoft Windows Server 2003 Standard Edition	9.3_M2		9.3 TS1M2
		Microsoft Windows Server 2003 Enterprise Edition	9.3_M2		9.3 TS1M2
		Microsoft Windows Server 2003 Datacenter Edition	9.3_M2		9.3 TS1M2
		Microsoft Windows 8.1 Pro x64	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		Microsoft Windows 8.1 Pro 32-bit	9.3_M2	9.4_M6	9.3 TS1M2	9.4 TS1M6
		z/OS	9.3_M2	9.4_M6	9.3 TS1M2	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.

Here is example PROC SQL code that replicates the problem:

---

proc sql;
create view v1 as
select t1.col1,t1.col2,  t1.col3, t1.col4,t1.col5
from mydblib.dbtable1 t1;

create view v2 as
select t1.col1, t1.col3,
        avg(t1.col2) AS 'sum_of_col2'n,
        sum(t1.col2) AS 'flg_col2'n
from v1 t1
group by t1.col1,  t1.col3;

create view v3 as
select t1.col1,
       1 as flg_col1
from mydblib.dbtable1 t1;

create view v4 as
select t1.col1,
       t1.'flg_col2'n AS flg_col6,
       (case
            when t2.col1 IS MISSING then '9'
            when t2.col1 NOT IS MISSING then t2.'flg_col2'n
            else ''
        end) AS flg_col7
from v1 t1
left join 
v1 t2
on (t1.col1 = t2.col1);

create table results as
select t1.flg_col6,
       t1.flg_col7,
      (count(t1.col1)) AS count_of_col1
from v4 t1
group by BY t1.flg_col6,
            t1.flg_col7;
quit;

A fix for this issue for Base SAS 9.4_M5 is available at:

https://tshf.sas.com/techsup/download/hotfix/HF2/B6Q.html#62752

---

A fix for this issue for Base SAS 9.4_M4 is available at:

https://tshf.sas.com/techsup/download/hotfix/HF2/A3Z.html#62752

---

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

https://tshf.sas.com/techsup/download/hotfix/HF2/I22.html#62752

---

Incorrect results are generated when you use a complex SQL query that has multiple functions.

Type:	Problem Note
Priority:	high

Date Modified:	2018-08-17 09:34:28
Date Created:	2018-08-13 15:56:59