BookmarkSubscribeRSS Feed
SujathaBalachandran
Calcite | Level 5

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

12 REPLIES 12
Kurt_Bremser
Super User

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

SujathaBalachandran
Calcite | Level 5

Thanks a lot!

I tried this and it worked perfect!

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

Sujatha

jakarman
Barite | Level 11

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
   

---->-- ja karman --<-----
Patrick
Opal | Level 21

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;

BrunoMueller
SAS Super FREQ

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;
SujathaBalachandran
Calcite | Level 5

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

Kurt_Bremser
Super User

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.

BrunoMueller
SAS Super FREQ

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

Kurt_Bremser
Super User

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?

BrunoMueller
SAS Super FREQ

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

jakarman
Barite | Level 11

47182 - Adding a leading zero to the %s directive in a PICTURE statement in PROC FORMAT does not ins...

take care of differences at releases and notes.

---->-- ja karman --<-----

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 12 replies
  • 6240 views
  • 6 likes
  • 5 in conversation