As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.
Here are some tips for How to share your SAS knowledge with your professional network.
Convert DB2 timestamp to SAS DATETIME25.6
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);
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); run;