38864 - Using the SASDATEFMT= data set option as an environment variable sometimes produces inconsistent behavior when date columns are used in a WHERE clause

Problem Note 38864: Using the SASDATEFMT= data set option as an environment variable sometimes produces inconsistent behavior when date columns are used in a WHERE clause

Using the SASDATEFMT= data set option as an environment variable sometimes can produce inconsistent behavior when date columns are used in WHERE clauses. Specifically, the inconsistent behavior can occur when you use SASDATEFMT=DATE. When SASDATEFMT is set to SAS DATE format, you might see the TRUNC function used in WHERE clauses for DATE columns because setting the SASDATEFMT option value to DATE format indicates that you are interested only in the date part of the datetime value in the DATE column.

However, the TRUNC function might be missing in scenarios where a performance-boosting feature overrides the regular SQL building code (this is called Implicit Pass-Through processing). For example, such an override occurs when an SQL procedure uses the keyword DISTINCT, aggregate functions [for example, COUNT(*) or SUM()], or table joins. You can see when this override occurs by using the SASTRACE= option to trace the events. When an override occurs, the following note appears in the trace log:

ACCESS ENGINE: SQL statement was passed to the DBMS for fetching data.

To illustrate this problem, assume that you have a table that contains a date column with one row, as shown below:

SQL> desc dtetab Name Null? Type ----------------------------------------- -------- ---------------- C1 DATE SQL> select to_char(c1, 'ddmonyyyy hh:mi:ss') from dtetab; TO_CHAR(C1,'DDMONY ------------------ 23feb2010 09:18:54

Now invoke SAS with the following command:

–set SASDATEFMT date

If you use the SASTRACE= option, as shown in the following example, you can see how adding the DISTINCT key in the SELECT statement changes the SQL and how one of the two WHERE clauses contains the TRUNC function:

libname x oracle user=user–name pw=password path=path–name; NOTE: Libref X was successfully assigned as follows: Engine: ORACLE Physical Name: alien options sastrace=",,,d" sastraceloc=saslog nostsuffix; proc sql; select * from dtetab; ERROR: File WORK.DTETAB.DATA does not exist. select * from x.dtetab; ORACLE_1: Prepared: on connection 0 SELECT * FROM DTETAB ORACLE_2: Executed: on connection 0 SELECT statement ORACLE_1 C1 âââââââââ 23FEB2010 select distinct * from x.dtetab; ORACLE_3: Prepared: on connection 0 SELECT * FROM DTETAB ORACLE_4: Prepared: on connection 0 select distinct dtetab."C1" from DTETAB ORACLE_5: Executed: on connection 0 SELECT statement ORACLE_4 ACCESS ENGINE: SQL statement was passed to the DBMS for fetching data. C1 âââââââââ 23FEB2010 select * from x.dtetab where c1='23Feb2010'd; ORACLE_6: Prepared: on connection 0 SELECT * FROM DTETAB ORACLE_7: Prepared: on connection 0 SELECT "C1" FROM DTETAB WHERE (TRUNC("C1") =TO_DATE('23FEB2010','DDMONYYYY','NLS_DATE_LANGUAGE=American') ) ORACLE_8: Executed: on connection 0 SELECT statement ORACLE_7 C1 âââââââââ 23FEB2010 select distinct * from x.dtetab where c1='23Feb2010'd; ORACLE_9: Prepared: on connection 0 SELECT * FROM DTETAB ORACLE_10: Prepared: on connection 0 select distinct dtetab."C1" from DTETAB where DTETAB."C1" = TO_DATE('23FEB2010','DDMONYYYY','NLS_DATE_LANGUAGE=American') ORACLE_11: Executed: on connection 0 SELECT statement ORACLE_10 ACCESS ENGINE: SQL statement was passed to the DBMS for fetching data. NOTE: No rows were selected.

To avoid this problem, you should use the DBSASTYPE= data set option instead of the SASDATEFMT= option. For details about the DBSASTYPE= option, see DBSASTYPE= Data Set Option in SAS/ACCESS (R) 9.2 for Relational Databases: Reference, Fourth Edition.

Operating System and Release Information

Product Family	Product	System	SAS Release
Product Family	Product	System	Reported	Fixed*
SAS System	SAS/ACCESS Interface to Oracle	Microsoft® Windows® for 64-Bit Itanium-based Systems	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter 64-bit Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise 64-bit Edition	9.1 TS1M3 SP4
		Microsoft Windows XP 64-bit Edition	9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Datacenter Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Professional	9.1 TS1M3 SP4
		Microsoft Windows NT Workstation	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3 SP4
		Microsoft Windows XP Professional	9.1 TS1M3 SP4
		Windows Vista	9.1 TS1M3 SP4
		64-bit Enabled AIX	9.1 TS1M3 SP4
		64-bit Enabled HP-UX	9.1 TS1M3 SP4
		64-bit Enabled Solaris	9.1 TS1M3 SP4
		HP-UX IPF	9.1 TS1M3 SP4
		Linux	9.1 TS1M3 SP4
		Linux on Itanium	9.1 TS1M3 SP4
		OpenVMS Alpha	9.1 TS1M3 SP4
		Solaris for x64	9.1 TS1M3 SP4
		Tru64 UNIX	9.1 TS1M3 SP4

* 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.

Type:	Problem Note
Priority:	high
Topic:	Data Management ==> Data Sources ==> External Databases ==> Oracle

Date Modified:	2011-05-13 10:55:03
Date Created:	2010-02-25 17:37:50

Support

Problem Note 38864: Using the SASDATEFMT= data set option as an environment variable sometimes produces inconsistent behavior when date columns are used in a WHERE clause

Operating System and Release Information