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-08.51.48.036386
  • 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);

Example

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);
 run;