In Base SAS^® software, incorrect results might occur when you run an SQL procedure query and the following conditions exist:

• You create view1, which references a database management system (DBMS) table and that includes a GROUP BY clause that contains two variables that are being renamed via a SELECT clause.
• You create view2 based on view1 in which you create a new variable based off one of the renamed variables from view1.
• You create view3 based on view2 in which two of the three variables from view2 are renamed and a third variable is summed. The GROUP BY clause includes the original names as they existed in view2.
• You are running one of the following versions:
  • SAS^® 9.4M4 (TS1M4) with either of these Base SAS hot fixes: A3Z058 or A3Z067
  • SAS^® 9.4M5 (TS1M5) with any of the following Base SAS hot fixes: B6Q027,B6Q044, B6Q045, B6Q056, B6Q071, B6Q073, or B6Q076 B6Q087
  • SAS^® 9.4M6 (TS1M6)

See the Full Code tab for an example of code that would encounter this problem.

In this scenario, instead of the data being simply summarized, the summarized results are "re-merged" with the original data, which results in duplicate rows. You can see an example of the incorrect results that are generated on the Output tab.

The only workaround is to add DISTINCT to the SELECT clause of view3 or to not rename the variables in view2.

Click the Hot Fix tab in this note to access the hot fix for this issue.

Operating System and Release Information

libname DBLIB ....;

proc sql noerrorstop;
  drop table DBlib.input_sample;
quit;

/* Sample data */
data DBLIB.input_sample;
  infile DATALINES dsd;
  input month number1 number2;
datalines;
11,100,1
10,100,2
11,100,3
10,100,4
11,200,5
11,200,6
10,200,7
11,200,8
11,300,9
11,300,10
10,300,11
11,300,12
11,400,13
10,400,14
10,400,15
11,400,16
10,500,17
11,500,18
10,500,19
11,500,20
;
run;

options sastrace=',,,d' sastraceloc=saslog nostsuffix;
proc sql;
  create view work.temp_view1 as
  select
    t1.month as month_A
    ,t1.number1 as number1_A
    ,SUM(t1.number2) as number2_A
  from DBLIB.input_sample t1
  where t1.month=10
  group by t1.month, t1.number1;
quit;

proc sql;
  create view work.temp_view2 as
  select
    t1.month_A
    ,(case
      when t1.number1_A = 500 then 2
      else 9
    end) as number1_B
    ,t1.number2_A
  from work.temp_view1 t1;
quit;

/* UNEXPECTED RESULT */
proc sql;
  create view work.UNEXPECTED_RESULT as
  select
     t1.month_A as MONTH
    ,t1.number1_B as NUMBER
    ,SUM(t1.number2_A) as number2_B
  from work.temp_view2 t1
  group by t1.month_A, t1.number1_B;
quit;


/* EXPECTED RESULT */
/* If I don't rename a column name, I can get the expected result. */
proc sql;
  create view work.EXPECTED_RESULT1 as
  select
     t1.month_A
    ,t1.number1_B
    ,SUM(t1.number2_A) as number2_B
  from work.temp_view2 t1
  group by t1.month_A, t1.number1_B;
quit;

/* EXPECTED RESULT */
/* Or if I add distinct, I can get the expected result. */
proc sql;
  create view work.EXPECTED_RESULT2 as
  select distinct
     t1.month_A as MONTH
    ,t1.number1_B as NUMBER
    ,SUM(t1.number2_A) as number2_B
  from work.temp_view2 t1
  group by t1.month_A, t1.number1_B;
quit;

Unexpected Results:                                 
                                                   
                                           
     Obs    MONTH         NUMBER    number2_B          
                                                   
       1      10               2           36          
       2      10               9           53          
       3      10               9           53          
       4      10               9           53          
       5      10               9           53          
                                                   
---------------------------------------------------
                                                   
Expected Results:                                   
                                                   
                                           
     Obs    month_A      number1_B    number2_B        
                                                   
       1       10                2           36        
       2       10                9           53          

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

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