Convert DB2 timestamp to SAS DATETIME25.6

From sasCommunity
Jump to: navigation, search

DB2 Timestamp problem

The SAS date and time formats does not support the DB2 timestamp format.

Converting from DB2 timestamp to SAS DATETIME25.6 must therefore be made in several steps with separate date and time conversion.

  • From 2007-06-23-
  • To 23JUN2007:08:51:48.036386

The code below encapsulates the necessary formatting steps into one single line of code.

 SAS_ts = input(compress(put(input(substr(DB2_ts,1,10), YYMMDD10.), DATE9.) || ':' || put(input(substr(DB2_ts,12,15), TIME15.6), TIME15.6)) , DATETIME25.6);

Historical dates

Since SAS uses the Gregorian calendar it only represents dates since 1582. However, DB2 uses a Proleptic Gregorian calendar to represent dates prior to 1582. When these pre-Gregorian calendar dates are converted from DB2, SAS produces missing values.


The following sample code replaces a DB2 timestamp $26 column named cre_ts with a SAS DATETIME25.6 column with the same name:

 data dds_se.x_mp_xml_rk (drop=db2_cre_ts);
    set dds_se.x_mp_xml_rk (rename=(cre_ts = db2_cre_ts));
    format cre_ts DATETIME25.6;
    cre_ts = input(compress(put(input(substr(db2_cre_ts,1,10), YYMMDD10.), DATE9.) || ':' || put(input(substr(db2_cre_ts,12,15), TIME15.6), TIME15.6)) , DATETIME25.6);

See also