The SASDATEFMT= option converts database management system (DBMS) data types to SAS DATE, TIME, or DATETIME data types. The DBSASTYPE= option also converts DBMS data types, not only to DATE, TIME, or DATETIME types, but also to CHAR and NUMERIC types.

Because the DBSASTYPE= option does the same as and more than the SASDATEFMT= option, it is expected that the SASDATEFMT= option will be phased out in a future release. As a result, support for the Oracle TIMESTAMP data type has not been included for the SASDATEFMT= option. If you attempt to use the SASDATEFMT= option with the TIMESTAMP data type, the option is ignored and no change occurs in the value, as shown in the following example:

libname ora oracle user=oracle-user password=oracle-password path=oracle-path;
proc sql noprint;
   create table work.sastabble
   as select * 
   from ora.oratable (sasdatefmt=(c1='date9.'));
quit;

When this code is submitted, the column C1 is not converted to the DATE9. format, and no warning is generated.

To avoid this issue, use the DBSASTYPE= option instead of the SASDATEFMT= option as shown in this example.

libname ors oracle user=oracle-user password=oracle-password path=oracle-path;
proc sql noprint;
   create table work.sastabble
   as select * 
   from ors.oratable (dbsastype=(c1='date9.'));
quit;

Operating System and Release Information