topic Re: Working with datetime in longitudinal data in SAS Programming

Working with datetime in longitudinal data

desireatem — Wed, 17 Mar 2021 00:28:08 GMT

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

DATETIME.

ANYDTDTM40

Re: Working with datetime in longitudinal data

ballardw — Wed, 17 Mar 2021 01:27:43 GMT

@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.

Re: Working with datetime in longitudinal data

desireatem — Wed, 17 Mar 2021 02:11:20 GMT

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

Re: Working with datetime in longitudinal data

Tom — Wed, 17 Mar 2021 03:28:03 GMT

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

Re: Working with datetime in longitudinal data

desireatem — Wed, 17 Mar 2021 03:43:17 GMT

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

Re: Working with datetime in longitudinal data

desireatem — Wed, 17 Mar 2021 04:06:59 GMT

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/

Re: Working with datetime in longitudinal data

ballardw — Wed, 17 Mar 2021 10:00:13 GMT

@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/ta-p/424354 has a PDF with much information about dates.

Re: Working with datetime in longitudinal data

Tom — Wed, 17 Mar 2021 12:19:58 GMT

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

Re: Working with datetime in longitudinal data

desireatem — Wed, 17 Mar 2021 14:42:17 GMT

THank you! This is correct.