Problem Note 42737: Incorrect results occur in implicit pass-through when you use CALCULATED keyword on GROUP BY statement
 |  |  |  |  |  |
With SAS® Scalable Data Server®, incorrect textualization of SQL implicit pass-through queries occurs when you use the CALCULATED keyword in a GROUP BY statement.
The code in the Full Code tab of this Problem Note demonstrates the incorrect results and produces the following message in the SAS log file:
NOTE: Implicit SQL: select distinct t1.col2, DATEPART(t1.col1) as col1, t1.col3, COUNT(*)
as num from TBL t1 group by t1.col2, col1, t1.col3
NOTE: Table WORK.SASDATA created, with 66085 rows and 4 columns.
The CALCULATED keyword is missing in the Implicit SQL note above.
Select 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.21_M3 | 9.3 | 9.2 TS2M3 | 9.3 TS1M0 |
| Linux for x64 | 9.21_M3 | 9.3 | 9.2 TS2M3 | 9.3 TS1M0 | ||
| HP-UX IPF | 9.21_M3 | 9.3 | 9.2 TS2M3 | 9.3 TS1M0 | ||
| 64-bit Enabled Solaris | 9.21_M3 | 9.3 | 9.2 TS2M3 | 9.3 TS1M0 | ||
| 64-bit Enabled HP-UX | 9.21_M3 | 9.3 | 9.2 TS2M3 | 9.3 TS1M0 | ||
| 64-bit Enabled AIX | 9.21_M3 | 9.3 | 9.2 TS2M3 | 9.3 TS1M0 | ||
| Windows 7 Ultimate 32 bit | 9.21_M3 | 9.3 | 9.2 TS2M3 | 9.3 TS1M0 | ||
| Microsoft Windows Server 2003 Standard Edition | 9.21_M3 | 9.3 | 9.2 TS2M3 | 9.3 TS1M0 | ||
| Microsoft Windows Server 2003 for x64 | 9.21_M3 | 9.3 | 9.2 TS2M3 | 9.3 TS1M0 | ||
| Microsoft Windows Server 2008 | 9.21_M3 | 9.3 | 9.2 TS2M3 | 9.3 TS1M0 | ||
| Microsoft Windows Server 2008 for x64 | 9.21_M3 | 9.3 | 9.2 TS2M3 | 9.3 TS1M0 | ||
| Microsoft Windows XP Professional | 9.21_M3 | 9.3 | 9.2 TS2M3 | 9.3 TS1M0 | ||
| Microsoft Windows Server 2003 Datacenter Edition | 9.21_M3 | 9.3 | 9.2 TS2M3 | 9.3 TS1M0 | ||
| Microsoft Windows Server 2003 Enterprise Edition | 9.21_M3 | 9.3 | 9.2 TS2M3 | 9.3 TS1M0 | ||
| Microsoft® Windows® for x64 | 9.21_M3 | 9.3 | 9.2 TS2M3 | 9.3 TS1M0 | ||
| Microsoft Windows XP 64-bit Edition | 9.21_M3 | 9.3 | 9.2 TS2M3 | 9.3 TS1M0 | ||
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.21_M3 | 9.3 | 9.2 TS2M3 | 9.3 TS1M0 | ||
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.21_M3 | 9.3 | 9.2 TS2M3 | 9.3 TS1M0 | ||
libname spdslib sasspds 'tmp' server=localhost.5400 user='anonymous' IP=YES;
data SPDSLIB.tbl;
format col1 datetime16.;
do i=1 to 1000000;
col2= round(1000*ranuni(1));
col1=1447213759 + round(500000*ranuni(2));
col3=round(10*ranuni(1));
output;
end;
drop i;
run;
PROC SQL;
CREATE TABLE WORK.sasdata AS
SELECT DISTINCT t1.col2, (datepart(t1.col1)) FORMAT=ddmmyy10. LABEL="col1" AS col1,
t1.col3, (count(*)) AS num
FROM SPDSLIB.tbl t1
GROUP BY t1.col2, (CALCULATED col1), t1.col3;
QUIT;
A fix for this issue for Base SAS 9.21_M3 is available at:
http://ftp.sas.com/techsup/download/hotfix/HF2/B25.html#42737A fix for this issue for Base SAS 9.3_M1 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/F98.html#42737
A hot fix is available for this problem.
| Type: | Problem Note |
| Priority: | alert |
| Date Modified: | 2011-08-26 18:01:41 |
| Date Created: | 2011-03-21 09:58:35 |