Data Set Options for Relational Databases

SASDATEFMT= Data Set Option

Changes the SAS date format of a DBMS column.

Default value:	DBMS-specific
Valid in:	DATA and PROC steps (when accessing DBMS data using SAS/ACCESS software)
DBMS support:	Aster nCluster, DB2 under UNIX and PC Hosts, Greenplum, HP Neoview, Informix, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, Sybase, Sybase IQ, Teradata

Syntax
Syntax Description
Details
Examples
See Also

Syntax

SASDATEFMT=(DBMS-date-col-1='SAS-date-format'
<... DBMS-date-col-n='SAS-date-format'>)

Syntax Description

DBMS-date-col: specifies the name of a date column in a DBMS table.
SAS-date-format: specifies a SAS date format that has an equivalent (like-named) informat. For example, DATETIME21.2 is both a SAS format and a SAS informat, so it is a valid value for the SAS-date-format argument.

Details

If the SAS column date format does not match the date format of the corresponding DBMS column, convert the SAS date values to the appropriate DBMS date values. Use the SASDATEFMT= option to convert date values from the default SAS date format to another SAS date format that you specify.

Use the SASDATEFMT= option to prevent date type mismatches in these circumstances:

during input operations to convert DBMS date values to the correct SAS DATE, TIME, or DATETIME values
during output operations to convert SAS DATE, TIME, or DATETIME values to the correct DBMS date values.

The column names specified in this option must be DATE, DATETIME, or TIME columns; columns of any other type are ignored.

The format specified must be a valid date format; output with any other format is unpredictable.

If the SAS date format and the DBMS date format match, this option is not needed.

The default SAS date format is DBMS-specific and is determined by the data type of the DBMS column. See the documentation for your SAS/ACCESS interface.

Note: For non-English date types, SAS automatically converts the data to the SAS type of NUMBER. The SASDATEFMT= option does not currently handle these date types. However, you can use a PROC SQL view to convert the DBMS data to a SAS date format as you retrieve the data, or use a format statement in other contexts. [cautionend]

Oracle: It is recommended that you use the DBSASTYPE= data set option instead of SASDATEFMT=.

Examples

In this example, the APPEND procedure adds SAS data from the SASLIB.DELAY data set to the Oracle table that is accessed by MYDBLIB.INTERNAT. Using SASDATEFMT=, the default SAS format for the Oracle column DATES is changed to the DATE9. format. Data output from SASLIB.DELAY into the DATES column in MYDBLIB.INTERNAT now converts from the DATE9. format to the Oracle format assigned to that type.

 libname mydblib oracle user=testuser password=testpass;
 libname saslib 'your-SAS-library';

 proc append base=mydblib.internat(sasdatefmt=(dates='date9.'))force
    data=saslib.delay;
 run;

In the next example, SASDATEFMT= converts DATE1, a SAS DATETIME value, to a Teradata date column named DATE1.

libname x teradata user=testuser password=testpass;

proc sql noerrorstop;
   create table x.dateinfo ( date1 date );
   insert into x.dateinfo 
   ( sasdatefmt=( date1='datetime21.') )
     values ( '31dec2000:01:02:30'dt );

In this example, SASDATEFMT= converts DATE1, a Teradata date column, to a SAS DATETIME type named DATE1.

libname x teradata user=testuser password=testpass;

data sas_local;
format date1 datetime21.;
set x.dateinfo( sasdatefmt=( date1='datetime21.') );
run;