dhms datetime conversion

[lalaktgrau]

Hi all,

I used the following code to combine date and time data to create a datetime variable. However, the datetime variable is off. Help!

DATA testing;
SET raw;
DATETIME=DHMS(DTDATE,0,0,DTTIME);
FORMAT DATETIME DATETIME16.;
RUN;

SAS Output

Obs dtDate dtTime DATETIME

01/10/2016	23:29:00	11JAN16:22:58:00
01/10/2016	23:30:00	11JAN16:23:00:00
01/10/2016	23:31:00	11JAN16:23:02:00
01/10/2016	23:32:00	11JAN16:23:04:00
01/10/2016	23:33:00	11JAN16:23:06:00
01/10/2016	23:34:00	11JAN16:23:08:00
01/10/2016	23:35:00	11JAN16:23:10:00
01/10/2016	23:36:00	11JAN16:23:12:00
01/10/2016	23:37:00	11JAN16:23:14:00
01/10/2016	23:38:00	11JAN16:23:16:

[RW9]

Please avoid coding all in uppercase, its like your shouting at me.  Also please use the code window for code - its the {i} above post.  This:
DATETIME=DHMS(DTDATE,0,0,DTTIME);

The dhms() function accepts:

dhms(<date>,number of hours,number of minutes,number of seconds)

You have put 0,0, then a very large number which is what is stored behind a time value.  Dates are number of days since cuttoff.  Time is number of seconds past midnight.  What you want to do is split the time per the function parameters:

datatime=dhms(dtdate,hour(dttime),minute(dttime),second(dttime));

[Tom · Accepted solution]

---

@RW9 wrote:

Please avoid coding all in uppercase, its like your shouting at me.  Also please use the code window for code - its the {i} above post.  This:
DATETIME=DHMS(DTDATE,0,0,DTTIME);

The dhms() function accepts:

dhms(<date>,number of hours,number of minutes,number of seconds)

You have put 0,0, then a very large number which is what is stored behind a time value.  Dates are number of days since cuttoff.  Time is number of seconds past midnight.  What you want to do is split the time per the function parameters:

datatime=dhms(dtdate,hour(dttime),minute(dttime),second(dttime));

---

If the value really is a TIME value (number of seconds since midnight)  then using 0,0,seconds is what you want to do.  Converting the seconds into hours, minutes and seconds and then passing them separately is really just wasted effort.

Note that the HOUR(), MINUTE() and SECOND() functions will work on both TIME and DATETIME values. Basically they ignore the "date" part of a datetime value (any value larger than 24 hours).  So you could also use one of these:

datatime=dhms(dtdate,0,0,timepart(dttime));
datatime=dhms(dtdate,0,0,mod(dttime,'24:00't));
datatime=dhms(dtdate,0,0,mod(dttime,24*60*60));

[ballardw]

When I run this code to simulate your data:

data check;
   informat dtdate mmddyy10. dttime time8.;
   format dtdate mmddyy10. dttime time8.;
   input dtdate dttime;
   DATETIME=DHMS(DTDATE,0,0,DTTIME);
   format datetime datetime18.;
datalines;
01/10/2016 23:29:00 
01/10/2016 23:30:00 
01/10/2016 23:31:00 
01/10/2016 23:32:00 
01/10/2016 23:33:00 
01/10/2016 23:34:00 
01/10/2016 23:35:00 
01/10/2016 23:36:00 
01/10/2016 23:37:00 
01/10/2016 23:38:00 
;
run;

I get

    dtdate      dttime              DATETIME

01/10/2016    23:29:00      10JAN16:23:29:00
01/10/2016    23:30:00      10JAN16:23:30:00
01/10/2016    23:31:00      10JAN16:23:31:00
01/10/2016    23:32:00      10JAN16:23:32:00
01/10/2016    23:33:00      10JAN16:23:33:00
01/10/2016    23:34:00      10JAN16:23:34:00
01/10/2016    23:35:00      10JAN16:23:35:00
01/10/2016    23:36:00      10JAN16:23:36:00
01/10/2016    23:37:00      10JAN16:23:37:00
01/10/2016    23:38:00      10JAN16:23:38:00

I would check your date variable to see if there is a decimal portion, which would represent a fraction of day or if the "time" value comes from an interval it may be exceeding 24 hours.

Note that if you run this code formatted value of date appears to match the one you show but when used with dhms you get quite a chunk of the day added to the time portion.

data junk;
   date = '01JAN2016'd + .5;
   put date date9.;
   datetime = dhms(date,0,0,0);
   put datetime datetime16.;
run;

[Tom]

It looks like for the example data posted that the "date" value does include the time as a fraction of a day (like how Excel would store it).  So adding the date with fraction of day and time caused the time to be doubled.

Here is test using the first two values in original question.

data have ;
  input date :mmddyy. time :time. datetime :datetime. ;
  date2 = date + (time/'24:00't);
  datetime2 = dhms(date,0,0,time);
  datetime3 = dhms(date2,0,0,time);
  datetime4 = dhms(date2,0,0,0);
  datetime5 = dhms(int(date2),0,0,time);
  format date date2 date9. time time8. datetime: datetime20. ;
  put (_n_ _all_) (=/);
cards;
01/10/2016 23:29:00 11JAN16:22:58:00
01/10/2016 23:30:00 11JAN16:23:00:00
;

_N_=1
date=10JAN2016
time=23:29:00
datetime=11JAN2016:22:58:00
date2=10JAN2016
datetime2=10JAN2016:23:29:00
datetime3=11JAN2016:22:58:00
datetime4=10JAN2016:23:29:00
datetime5=10JAN2016:23:29:00

_N_=2
date=10JAN2016
time=23:30:00
datetime=11JAN2016:23:00:00
date2=10JAN2016
datetime2=10JAN2016:23:30:00
datetime3=11JAN2016:23:00:00
datetime4=10JAN2016:23:30:00
datetime5=10JAN2016:23:30:00