SASDATEFMT= Data Set Option

Changes the SAS date format of a DBMS column.

Valid in:	DATA and PROC steps
Default:	DBMS-specific
Supports:	All

Syntax

Syntax Description

Details

Examples

Example 1: Changing the Date Format in Oracle

Example 2: Changing a SAS Date Format to a Teradata Format

Example 3: Changing a Teradata Date Format to a SAS Format

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 date format of a SAS column does not match the date format of the corresponding DBMS column, you must convert the SAS date values to the appropriate DBMS date values. The SASDATEFMT= option enables you 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 the following 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 that are 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 data source.

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.

Oracle details: It is recommended that the DBSASTYPE= data set option be used instead of SASDATEFMT=.

Examples

Example 1: Changing the Date Format in Oracle

In the following 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 fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1
   dsn=oradsn dsnuser=orauser dsnpwd=orapwd;
libname saslib 'your-SAS-library';
proc append base=mydblib.internat (sasdatefmt=(dates='date9.'))
   force data=saslib.delay;
run;

Example 2: Changing a SAS Date Format to a Teradata Format

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

libname x fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1
   dsn=teradsn dsnuser=terauser dsnpwd=terapwd;
proc sql noerrorstop;
   create table x.dateinfo ( date1 date );
   insert into x.dateinfo
   (sasdatefmt=( date1='datetime21.') )
    values ( '31dec2000:01:02:30'dt );

Example 3: Changing a Teradata Date Format to a SAS Format

In the following example, SASDATEFMT= converts Date1 (a Teradata date column) to a SAS DATETIME type named DATE1.

libname x fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1
   dsn=teradsn dsnuser=terauser dsnpwd=terapwd;
data sas_local;
   format date1 datetime21.;
   set x.dateinfo (sasdatefmt=(date1='datetime21.'));
run;