210 SASDATEFMT= Data Set Option Chapter 10
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 specified in this option must be DATE/DATETIME/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, but 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 “DBSASTYPE= Data Set Option” on page 185
be used instead of SASDATEFMT=.
Examples
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 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 following 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 the following 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.’) );
