In SAS^® 9.4M9 (TS1M9) with Hot Fix PF7F006 or later, an Oracle error might occur when all of the following are true:

• You use a WHERE clause in the SQL procedure, the WHERE statement in the DATA step or a different procedure, or the WHERE= data set option.
• You use the COALESCE function on two Oracle timestamp columns with the BETWEEN operator and do not use SAS datetime constants or other Oracle timestamp variables. Here is an example: 

where coalesce(TimeStamp1,TimeStamp2) between 2095515600 and 2095515601 

• You use the Oracle LIBNAME engine to access the Oracle table.

The error might look similar to the following:

ERROR: ORACLE prepare error: ORA-00932: inconsistent datatypes: expected TIMESTAMP got NUMBER 

Cause

With the SQL procedure, the error occurs only if you have turned on tracing via the SASTRACE= system option. The query does not fail because of that error. Instead of the entire query being passed to Oracle, the WHERE clause is not included with the query being passed to the database and the WHERE is processed once the data is retrieved by SAS. 

With the DATA step and with other procedures, SAS stops processing and issue notes similar to the following because of the error:

NOTE: The DATA step has been abnormally terminated.
NOTE: The SAS System stopped processing this step because of errors.

Workaround

The workaround for this issue is to use a SAS date constant instead of the internal value (the number of seconds since 01JAN1960 at midnight).  

After the application of the hot fix, the SQL procedure behavior does not change. It still initially issues the error and continues to process the query. The workaround of using a date constant is still an option for the SQL procedure. For the DATA step and the other procedures, the error no longer occurs and the correct WHERE is passed to the database.