Time since last observation

[pdick2]

Hello!

I have a dataset that I would like to get the time difference from one observed value from the last observation value by patient_ID. Here is an example of what I want:.

data have;
input Hour HR Temp SepsisLabel Patient_ID onset_time time_difference;
datalines;
0 88 36.11 0 34 -13
1 88 36.17 0 34 -12
2 88 .  0 34 -11
3 83.5 . 0 34 -10
4 80 .  0 34 -9
5 88 36.5 0 34 -8
6 91 . 0 34 -7
7 88 . 0 34 -6
8 80 .  0 34 -5
9 80 . 0 34 -4
10 80 . 0 34 -3
11 82 . 0 34 -2
12 77 . 0 34 -1
0 88 36 0 40 -5
1 88 36 0 40 -4
2 88 36 0 40 -3
3 88 36 0 40 -2
4 88 36 0 40 -1
;

Please let me know what's the easiest way to get this! Chatgpt is struggling to understand how lag functions work, and so am I.

Thanks!

[Patrick]

Based on your data please explain the logic in detail for calculation of such differences.

For example can you please explain how exactly you calculate time_difference= 5 with below data?

[pdick2]

data have;
input Hour HR Temp SepsisLabel Patient_ID time_difference;
datalines;
0 88 36.11 0 34 -13
1 88 36.17 0 34 -12
2 88 .  0 34 -11
3 83.5 . 0 34 -10
4 80 .  0 34 -9
5 88 36.5 0 34 -8
6 91 . 0 34 -7
7 88 . 0 34 -6
8 80 .  0 34 -5
9 80 . 0 34 -4
10 80 . 0 34 -3
11 82 . 0 34 -2
12 77 . 0 34 -1
0 88 36 0 40 -5
1 88 36 0 40 -4
2 88 36 0 40 -3
3 88 36 0 40 -2
4 88 36 0 40 -1
;

 Opps. I left the "onset_time" from a input template from a previous dataset. This line of code should make more sense. Apologies!

[Patrick]

It would really help if you could also explain the logic/formula in words for at least one or two of your observations. 

[pdick2]

Lets adjust that. Here is something that might be easier to work with:

data have;
input Hour HR Temp SepsisLabel Patient_ID time_difference;
datalines;
0 88 36.11 0 34 -12
1 88 36.17 0 34 -11
2 88 .  0 34 -10
3 83.5 . 0 34 -9
4 80 .  0 34 -8
5 88 36.5 0 34 -7
6 91 . 0 34 -6
7 88 . 0 34 -5
8 80 .  0 34 -4
9 80 . 0 34 -3
10 80 . 0 34 -2
11 82 . 0 34 -1
12 77 . 0 34 0
0 88 36 0 40 -4
1 88 36 0 40 -3
2 88 36 0 40 -2
3 88 36 0 40 -1
4 88 36 0 40 0
;

What I want is to label the hours since the last observation. For example, patient_ID=40 has their last observed hour = 4. This will be time_difference = 0. Then the previous row will have time_difference = -1 (coming from 3-4 = -1).

So ideally it would look something like this (for each unique patient_ID):

time_difference = (last observed hour for that unique patient_ID) - (hour of interest for that unique patient_ID) = a negative time difference per row.

I think this will be easier if I do it in the format above, since I have another dataset in a similar format.

Let me know if you need anything else, and thanks for helping! I'm slowly learning my way around SAS..

[Patrick · Accepted solution]

Below one way how to do this.

data have;
  input Hour HR Temp SepsisLabel Patient_ID time_difference;
datalines;
0 88 36.11 0 34 -12
1 88 36.17 0 34 -11
2 88 .  0 34 -10
3 83.5 . 0 34 -9
4 80 .  0 34 -8
5 88 36.5 0 34 -7
6 91 . 0 34 -6
7 88 . 0 34 -5
8 80 .  0 34 -4
9 80 . 0 34 -3
10 80 . 0 34 -2
11 82 . 0 34 -1
12 77 . 0 34 0
0 88 36 0 40 -4
1 88 36 0 40 -3
2 88 36 0 40 -2
3 88 36 0 40 -1
4 88 36 0 40 0
;

data last_hour_per_patient;
  set have;
  by Patient_ID hour;
  if last.Patient_ID;
  last_hour=hour;
  keep Patient_ID last_hour;
run;

data want;
  merge have last_hour_per_patient;
  by patient_id;
  time_diff_derived=hour-last_hour;
  drop last_hour;
run;

 Here a SQL approach:

proc sql;
  create table want2 as
  select l.*, l.hour-r.hour as time_diff_derived
  from have l
  inner join
  (select patient_id, max(hour) as hour from have group by patient_id) as r
  on l.patient_id=r.patient_id
  ;
quit;

 And here a 3rd option using a "double dow loop" ...well "dou" actually. (dow loop is not official SAS terminology but you will find white papers about it).

data want;
  do until(last.patient_id);
    set have(keep=Patient_ID hour);
    by Patient_ID hour;
  end;
  last_hour=hour;
  do until(last.patient_id);
    set have;
    by Patient_ID hour;
    time_difference_derived=hour-last_hour;
    drop last_hour;
    output;
  end;
run;

[pdick2]

1st set of codes worked! Thanks so much!