Working with datetime in longitudinal data

[desireatem]

I have the DateTime variable in a longitudinal data and i want to create two variable t1 and t2 as below:

id                     Datetime                                 t1                                                          t2       

1               8/13/2020 10:47                            0

1               8/13/2020 15:04           8/13/2020 15:04 - 8/13/2020 10:47                8/13/2020 15:04 - 8/13/2020 10:47

1               8/13/2020  19:08          8/13/2020 15:04 - 8/13/2020  19:08              8/13/2020 10:47 - 8/13/2020  19:08

NB: 

DateTime

Num

8

DATETIME.

ANYDTDTM40

[ballardw]

---

@desireatem wrote:

I have the DateTime variable in a longitudinal data and i want to create two variable t1 and t2 as below:

id                     Datetime                                 t1                                                          t2       

1               8/13/2020 10:47                            0

1               8/13/2020 15:04           8/13/2020 15:04 - 8/13/2020 10:47                8/13/2020 15:04 - 8/13/2020 10:47

1               8/13/2020  19:08          8/13/2020 15:04 - 8/13/2020  19:08              8/13/2020 10:47 - 8/13/2020  19:08

NB: 

DateTime

Num

8

DATETIME.

ANYDTDTM40

---

Do you want to do the subtraction indicated or make a hyphenated text value?

If it is subtraction do you understand the result will be in seconds?

What is the difference between t1 and t2 for the second row? You need to provide some rules if this is to have general solution.

[desireatem]

Hello , I want the subtraction indicated above. I do prefer  the difference in hours not second

[Tom]

I don't see a pattern in your original post.

But here is a something that does have a pattern.  T1 is the difference from the previous observations. T2 is the difference from the observation before that.

data have;
  input id datetime :datetime. ;
  format datetime datetime19. ;
cards;
1 13AUG2020:10:47
1 13AUG2020:15:04
1 13AUG2020:19:08
2 01JAN2021:01:00
2 02JAN2021:13:00
2 03JAN2021:01:00
2 03JAN2021:01:10
;

data want;
  set have ;
  by id;
  row+1;
  if first.id then row=1;

  lag_dt = lag(datetime);
  lag_dt2 = lag2(datetime);
  format lag_dt lag_dt2 datetime19.;

  if row > 1 then t1 = datetime - lag_dt;
  else lag_dt=.;
  if row > 2 then t2 = datetime - lag_dt2;
  else lag_dt2=.;

  format t1 t2 time12.;
run;

proc print;
run;

Obs  id             datetime  row               lag_dt              lag_dt2            t1            t2

 1    1   13AUG2020:10:47:00   1                     .                    .             .             .
 2    1   13AUG2020:15:04:00   2    13AUG2020:10:47:00                    .       4:17:00             .
 3    1   13AUG2020:19:08:00   3    13AUG2020:15:04:00   13AUG2020:10:47:00       4:04:00       8:21:00
 4    2   01JAN2021:01:00:00   1                     .                    .             .             .
 5    2   02JAN2021:13:00:00   2    01JAN2021:01:00:00                    .      36:00:00             .
 6    2   03JAN2021:01:00:00   3    02JAN2021:13:00:00   01JAN2021:01:00:00      12:00:00      48:00:00
 7    2   03JAN2021:01:10:00   4    03JAN2021:01:00:00   02JAN2021:13:00:00       0:10:00      12:10:00

[desireatem]

Thank you. Is it possible to change the time from 4:17:00, that is 4 hours 17 minutes to 4.28hours?

4:17:00

[desireatem]

I do not think you code works correctly. I made a mistake, 

I have the DateTime variable in a longitudinal data and i want to create two variable  t1 and t2  which are the absolute difference as below. For t1,  I am subtracting subsequent time from the first time while for t2 I am subtracting the subsequent time. In both cases, I do their absolute values so that, they are positive. I wish to have time in hours, that is 4.28 rather than 4:17:00.

id                     Datetime                                 t1                                                          t2       

1               8/13/2020 10:47                            0

1               8/13/2020 15:04          abs /8/13/2020 15:04 - 8/13/2020 10:47/              abs / 8/13/2020 15:04 - 8/13/2020 10:47/

1               8/13/2020  19:08         abs /8/13/2020 15:04 - 8/13/2020  19:08/            abs  /8/13/2020 10:47 - 8/13/2020  19:08/

[Tom · Accepted solution]

To get the magnitude of the difference use the ABS() function.  To convert seconds into hours just divide by the number of seconds in an hour.

Is this what you are trying to calculate?

data want;
  set have ;
  by id;
  row+1;
  if first.id then row=1;
  if row=1 then first_dt = datetime;
  retain first_dt ;
  lag_dt = lag(datetime);
  format lag_dt first_dt datetime19.;
  if row > 1 then do;
     lag_abs_hours = abs(datetime - lag_dt)/'01:00't;
     absolute_hours=abs(datetime - first_dt)/'01:00't;
  end;
  else do;
     lag_abs_hours = 0;
     absolute_hours= 0;
  end;
 
run;

                                                                                   lag_abs_   absolute_
Obs   id              datetime   row              first_dt                lag_dt     hours      hours

 1     1    13AUG2020:10:47:00    1     13AUG2020:10:47:00                     .     0.0000     0.0000
 2     1    13AUG2020:15:04:00    2     13AUG2020:10:47:00    13AUG2020:10:47:00     4.2833     4.2833
 3     1    13AUG2020:19:08:00    3     13AUG2020:10:47:00    13AUG2020:15:04:00     4.0667     8.3500
 4     2    01JAN2021:01:00:00    1     01JAN2021:01:00:00    13AUG2020:19:08:00     0.0000     0.0000
 5     2    02JAN2021:13:00:00    2     01JAN2021:01:00:00    01JAN2021:01:00:00    36.0000    36.0000
 6     2    03JAN2021:01:00:00    3     01JAN2021:01:00:00    02JAN2021:13:00:00    12.0000    48.0000
 7     2    03JAN2021:01:10:00    4     01JAN2021:01:00:00    03JAN2021:01:00:00     0.1667    48.1667

[desireatem]

THank you! This is correct.

[ballardw]

---

@desireatem wrote:

Thank you. Is it possible to change the time from 4:17:00, that is 4 hours 17 minutes to 4.28hours?

4:17:00

---

Are you going to want to use any SAS time related functions or formats on the result? If so don't.

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/... has a PDF with much information about dates.