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 |
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 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.
Hello , I want the subtraction indicated above. I do prefer the difference in hours not second
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
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 |
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/
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
THank you! This is correct.
@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.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.