10-24-2014 01:23 AM
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.
other = '%Y-%0m-%0d:%0H:%0M:%0S' (datatype=datetime);
Please let me know your ideas to resolve this issue.
10-24-2014 05:46 AM
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
10-24-2014 06:59 AM
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
10-25-2014 05:15 AM
If you replace the '%0S' directive with '%0s' then things should work (so: lower case instead of upper case 's').
other = '%Y-%0m-%0d-%0H:%0M:%0s' (datatype=datetime);
format dttm_val best32. dttm_pict db2date26.5;
proc print data=have;
10-27-2014 05:56 AM
Hi You can build your own format using Proc Format, see an example below:
10-27-2014 10:19 AM
Thank you for the suggestion!.
I tried the below coding
picture db2date (default=26)
other = '%Y-%0m-%0d:%0H:%0M:%0s' (datatype=datetime)
create mySasTbl as
where cond1 = true;
proc print data=mySasTbl label noobs;
format timstmp1 db2date26.; -> db2date26. generates report as 2014-10-24-10:44:
format myTblField db2date6.; -> db2date6. generates report as 2014-1
any suggestion is of great help!
10-27-2014 11:41 AM
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.
10-27-2014 03:18 PM
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
10-28-2014 02:21 AM
Does not work, at least in 9.2
(the puts from the log, using exactly your code)
In which environment did you test it?
10-28-2014 12:53 PM
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
10-29-2014 03:00 AM
OK, then there is an obvious difference in how SAS handles the picture directives between 9.2 and 9.3/9.4
10-27-2014 03:29 PM
take care of differences at releases and notes.