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

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:

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

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