calculate time difference in one column

[sophielr]

Hi, Appreciate any help for this. how to get NRS score 8 for timepoint 1 and medication O with SAS?

subject	timepoint(hour)	time of assessment	NRS score	medication	want output(NRS score)
101-0011	0	09:00:59	6
101-0011	1	11:13:00	3
101-0011	1	11:21:00	8	O	8  (11:21:00+4:00:00=15:21:00  all time of assessment below this row and happened before 15:21:00 are consider as timepoint 1 group. pick up the maximum NRS score 8 as output )
101-0011	2	12:13:00	7
101-0011	4	13:59:59	6	O
101-0011	4	14:20:00	8
101_0011	6	16:10:33	9

[yabwon]

Could you explain the logic behind what you want to achieve?

Why record for 11:21:00 is more "special" than the one form 13:59:59 (which also have "O")?

Bart

[sophielr]

Hi@yabwon, the data I got from my boss. I picked up parts of the raw data. the second "O" is for another time point. 

[yabwon]

Sorry for auto-quoting but: "Could you explain the logic behind what you want to achieve?"

B.

[sophielr]

Hi @yabwon ,

I hope you understand my explanation. for timepoint 1 and medication A: assessment time 11:21:00+4:00:00=15:21:00,  all times of assessment below this row and happened before 15:21:00 are considered as timepoint 1 group. pick up the maximum NRS score 8 in this group as output

Thank you

[PaigeMiller]

I was going to ask: then why don't we created wanted output for row 6, which also has medication O? I think this is what @yabwon  wants to know as well.

But I am not going to ask that. We have reached a point, just like in your last thread, where we have to ask 20 questions to get a clear understanding of the problem. This is not a good way to get the help you need.

@sophielr — would you please write a complete and clear explanation of the entire problem? Please do that rather than us asking question and follow-up question and another follow-up question. Emphasis on COMPLETE. Emphasis on CLEAR.  Emphasis on COMPLETE. Emphasis on COMPLETE. Emphasis on COMPLETE.

[sophielr]

Hi @PaigeMiller,

yes, I need to calculate the row 6 score also. I want to make my question easy so I will do the next part if I know how to calculate the first one.

[PaigeMiller]

---

@sophielr wrote:

Hi @PaigeMiller,

yes, I need to calculate the row 6 score also. I want to make my question easy so I will do the next part if I know how to calculate the first one.

---

I don't want answers to the specific questions that have been asked. I have already said that I want a COMPLETE and CLEAR explanation of the problem so we don't have to keep asking more questions, and so we don't have to keep scrolling up and down to understand your answers and how they relate to the data.

[yabwon]

You wan to "see into the future" for 4 hours, what about "the past"? is it "select all before" or maybe there is some limitation ?

Bart

[sophielr]

Hi @yabwon ,

I do not need to consider "the past time" right now. I only want to know how to find the rows in the calculated range( recorded time +4hours) and then find maximum number in this range.

Thank you

[yabwon · Accepted solution]

I did my best guess:

data have;
infile cards missover;
input subject : $ 20. timepoint time_of_assessment time8. NRS_score medication $;
format time_of_assessment time.;
cards;
101-0011 0 09:00:59 6    
101-0011 1 11:13:00 3    
101-0011 1 11:21:00 8 O 
101-0011 2 12:13:00 7    
101-0011 4 13:59:59 6 O  
101-0011 4 14:20:00 8    
101-0011 6 16:10:33 9  
101-0012 0 09:00:59 6    
101-0012 1 11:13:00 3    
101-0012 1 11:21:00 8 O 
101-0012 2 12:13:00 7    
101-0012 4 17:59:59 6 O  
101-0012 4 18:20:00 1    
101-0012 6 19:10:33 3 
;
run;
proc print;
run;

data have1;
  set have;
  where medication="O";
    
  rangelow = 0;
  rangeup  = time_of_assessment + 4*60*60;

  keep subject time_of_assessment medication rangelow rangeup;
run;
proc print;
run;

proc sql;
  create table have2 as
  select a.subject, a.time_of_assessment, max(b.NRS_score) as max_score
  from have1 as a, have as b
  where a.subject = b.subject 
    and b.time_of_assessment between a.rangelow and a.rangeup
  group by a.subject, a.time_of_assessment
  order by a.subject, a.time_of_assessment
  ;
quit;
proc print;
run;

data want;
  merge have have2;
  by subject time_of_assessment;
run;
proc print;
run;

Bart

[sophielr]

Thank you @yabwon, you give me a big help.

[PaigeMiller]

11:21:00+4:00:00=15:21:00  all time of assessment below this row and happened before 15:21:00

Where does the 4 hours come from?

What would happen if row 6 at time 14:20:00 had an NRS Score of 10? Would then the wanted output on row 3 be 10?

[sophielr]

Hi @PaigeMiller ,

4 hours is the criteria according to this trial document. the wanted score should be 10 if row 6 at time 14:20:00 had an NRS Score of 10.