topic Re: DB2 datetime format in SAS Data Management

DB2 datetime format

SujathaBalachandran — Fri, 24 Oct 2014 05:23:08 GMT

Hi,

I'm trying to generate an xl report from DB2 table using SAS. Could you please help me achieve the timestamp field in the DB2 format eg: 2014-10-24-10:44:25:56473.

I tried using the PICTURE FORMAT as below. But this formats up to Seconds. The fraction part of the seconds are not getting generated in the report.

proc format;

picture db2date

other = '%Y-%0m-%0d:%0H:%0M:%0S' (datatype=datetime);

run;

Please let me know your ideas to resolve this issue.

Sujatha

Re: DB2 datetime format

Kurt_Bremser — Fri, 24 Oct 2014 09:46:43 GMT

newval = put(datepart(oldval),yymmddd10.)!!'-'!!put(timepart(oldval),time15.6);

You may need to take care of blanks for hours<10

fixed "d" for dash/hyphen

Re: DB2 datetime format

jakarman — Fri, 24 Oct 2014 10:59:45 GMT

When you have SAS/Access Db2 there is an automatic conversion done. SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Fifth Edition

Is this what your question is about?

Within DB2 there are date/time functions and an automatic conversion is done to some internal format. DB2 Basics: Fun with Dates and Times

Or is it this area.

Dates/Tiems got an ISO standard 8601, that includes a l lot. SAS(R) 9.4 Formats and Informats: Reference

Re: DB2 datetime format

Patrick — Sat, 25 Oct 2014 09:15:49 GMT

If you replace the '%0S' directive with '%0s' then things should work (so: lower case instead of upper case 's').

Base SAS(R) 9.4 Procedures Guide, Third Edition

proc format;

picture db2date

other = '%Y-%0m-%0d-%0H:%0M:%0s' (datatype=datetime);

run;

data have;

format dttm_val best32. dttm_pict db2date26.5;

dttm_val=input('24Oct2014 10:44:25.56473',datetime26.5);

dttm_pict=dttm_val;

run;

proc print data=have;

run;

Re: DB2 datetime format

BrunoMueller — Mon, 27 Oct 2014 09:56:56 GMT

Hi You can build your own format using Proc Format, see an example below:

proc format; 
  picture db2date (default=26)
    other = '%Y-%0m-%0d:%0H:%0M:%0s' (datatype=datetime)
  ;
run; 

data _null_; 
  now = datetime();
  now2 = now;
  format now db2date.6 now2 datetime25.6; 
  putlog (now now2) (=/);
run;

Re: DB2 datetime format

SujathaBalachandran — Mon, 27 Oct 2014 14:19:40 GMT

Thank you for the suggestion!.

I tried the below coding

proc format;
picture db2date (default=26)
other = '%Y-%0m-%0d:%0H:%0M:%0s' (datatype=datetime)
;
run;

proc sql;

create mySasTbl as

select timstmp1

from db2table1

where cond1 = true;

run;

proc print data=mySasTbl label noobs;

format timstmp1 db2date26.; -> db2date26. generates report as 2014-10-24-10:44:

run;

format myTblField db2date6.; -> db2date6. generates report as 2014-1

any suggestion is of great help!

Thanks!

Sujatha

Re: DB2 datetime format

Kurt_Bremser — Mon, 27 Oct 2014 15:41:27 GMT

Since the picture format directive for seconds (%s) displays only 2 digits for seconds, and one cannot combine the directives with .9999 for digits after the comma, you may need to employ my previously posted method.

Re: DB2 datetime format

BrunoMueller — Mon, 27 Oct 2014 19:18:46 GMT

You must use the format as seen in my example, like

format now db2date.6 now2 datetime25.6;

It is important to specify the decimals, the number after the period

Re: DB2 datetime format

jakarman — Mon, 27 Oct 2014 19:29:09 GMT

47182 - Adding a leading zero to the %s directive in a PICTURE statement in PROC FORMAT does not insert a leading zero as documented

take care of differences at releases and notes.

Re: DB2 datetime format

Kurt_Bremser — Tue, 28 Oct 2014 06:21:38 GMT

Does not work, at least in 9.2

(the puts from the log, using exactly your code)

now=2014-10-28:07:16:

now2=28OCT2014:07:16:55.128662

In which environment did you test it?

Re: DB2 datetime format

SujathaBalachandran — Tue, 28 Oct 2014 13:51:12 GMT

Thanks a lot!

I tried this and it worked perfect!

newval = put(datepart(oldval),yymmddd10.)!!'-'!!put(timepart(oldval),tod15.6);

Sujatha

Re: DB2 datetime format

BrunoMueller — Tue, 28 Oct 2014 16:53:52 GMT

I used SAS9.4M2, where this works just fine.

See also Base SAS(R) 9.3 Procedures Guide, Second Edition where it shows how to use a user written function to format values. But only with SAS9.3

Re: DB2 datetime format

Kurt_Bremser — Wed, 29 Oct 2014 07:00:29 GMT

OK, then there is an obvious difference in how SAS handles the picture directives between 9.2 and 9.3/9.4