Problem Note 65537: An SQL procedure might generate incorrect results when a variable is renamed in a query
 |  |  |  |  |  |  |
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
| Product Family | Product | System | SAS Release | |
| Reported | Fixed* | |||
| SAS System | Base SAS | z/OS | 9.4 TS1M6 | |
| z/OS 64-bit | 9.4 TS1M6 | |||
| Microsoft® Windows® for x64 | 9.4 TS1M6 | |||
| Microsoft Windows 8 Enterprise 32-bit | 9.4 TS1M6 | |||
| Microsoft Windows 8 Enterprise x64 | 9.4 TS1M6 | |||
| Microsoft Windows 8 Pro 32-bit | 9.4 TS1M6 | |||
| Microsoft Windows 8 Pro x64 | 9.4 TS1M6 | |||
| Microsoft Windows 8.1 Enterprise 32-bit | 9.4 TS1M6 | |||
| Microsoft Windows 8.1 Enterprise x64 | 9.4 TS1M6 | |||
| Microsoft Windows 8.1 Pro 32-bit | 9.4 TS1M6 | |||
| Microsoft Windows 8.1 Pro x64 | 9.4 TS1M6 | |||
| Microsoft Windows 10 | 9.4 TS1M6 | |||
| Microsoft Windows Server 2012 Datacenter | 9.4 TS1M6 | |||
| Microsoft Windows Server 2012 R2 Datacenter | 9.4 TS1M6 | |||
| Microsoft Windows Server 2012 R2 Std | 9.4 TS1M6 | |||
| Microsoft Windows Server 2012 Std | 9.4 TS1M6 | |||
| Microsoft Windows Server 2016 | 9.4 TS1M6 | |||
| Microsoft Windows Server 2019 | 9.4 TS1M6 | |||
| Windows 7 Enterprise 32 bit | 9.4 TS1M6 | |||
| Windows 7 Enterprise x64 | 9.4 TS1M6 | |||
| Windows 7 Home Premium 32 bit | 9.4 TS1M6 | |||
| Windows 7 Home Premium x64 | 9.4 TS1M6 | |||
| Windows 7 Professional 32 bit | 9.4 TS1M6 | |||
| Windows 7 Professional x64 | 9.4 TS1M6 | |||
| Windows 7 Ultimate 32 bit | 9.4 TS1M6 | |||
| Windows 7 Ultimate x64 | 9.4 TS1M6 | |||
| 64-bit Enabled AIX | 9.4 TS1M6 | |||
| 64-bit Enabled Solaris | 9.4 TS1M6 | |||
| HP-UX IPF | 9.4 TS1M6 | |||
| Linux for x64 | 9.4 TS1M6 | |||
| Solaris for x64 | 9.4 TS1M6 | |||
Here is one example of a query that might generate the results. In the code below, you should update the LIBNAME statement to point to your database.
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:
https://tshf.sas.com/techsup/download/hotfix/HF2/D9T.html#65537A fix for this issue for Base SAS 9.4_M5 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/B6Q.html#65537| Type: | Problem Note |
| Priority: | high |
| Date Modified: | 2020-02-20 10:58:19 |
| Date Created: | 2020-02-11 09:42:38 |