topic Re: calculate time difference in one column in SAS Programming

calculate time difference in one column

sophielr — Mon, 24 Apr 2023 14:03:23 GMT

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

Re: calculate time difference in one column

yabwon — Mon, 24 Apr 2023 14:22:00 GMT

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

Re: calculate time difference in one column

PaigeMiller — Mon, 24 Apr 2023 14:28:49 GMT

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?

Re: calculate time difference in one column

sophielr — Mon, 24 Apr 2023 14:29:32 GMT

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.

Re: calculate time difference in one column

yabwon — Mon, 24 Apr 2023 14:31:57 GMT

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

Re: calculate time difference in one column

sophielr — Mon, 24 Apr 2023 14:36:14 GMT

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.

Re: calculate time difference in one column

sophielr — Mon, 24 Apr 2023 14:41:09 GMT

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

Re: calculate time difference in one column

PaigeMiller — Mon, 24 Apr 2023 14:50:25 GMT

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.

Re: calculate time difference in one column

yabwon — Mon, 24 Apr 2023 14:55:42 GMT

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

Re: calculate time difference in one column

sophielr — Mon, 24 Apr 2023 14:58:15 GMT

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.

Re: calculate time difference in one column

sophielr — Mon, 24 Apr 2023 15:02:57 GMT

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

Re: calculate time difference in one column

PaigeMiller — Mon, 24 Apr 2023 15:03:58 GMT

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

Re: calculate time difference in one column

yabwon — Mon, 24 Apr 2023 15:07:47 GMT

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

Re: calculate time difference in one column

sophielr — Mon, 24 Apr 2023 15:17:33 GMT

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