find max score in a range

[sophielr]

subject	timepoint(hour)	time of assessment	NRS score	medication	how to get hour 1	how to get hour 4
101-0011	0	09:00:59	6
101-0011	1	11:13:00	3		the green time happened before the medication record, so they are one group. The maximum is 6
101-0011		11:21:00	8	O	11:21:00+4:00:00=15:21:00
101-0011	2	12:13:00	7		these red time records before 15:21:00, so set them as one group. 8 is the maximum number inside, so we pick up 8
101-0011	4	13:59:59	6		I pick up smaller ones between the upper group and down group, so it is 6
101-0011		14:20:00😁	8	A	14:20:00+6:00:00=20:20:00	all small faces in one group because they happened before 20:20:00. The maximum score is 9. All assessment time before hour 4 is one group, the maximum is 8. The same, pick up the smaller one between 8 and 9 as hour 4 score
101-0011	6	16:15:00😁	8
101-0011		18:00:00😁	7	O	18:00:00+4:00:00=22:00:00
101-0011	8	18:15:00😁	8		these purple time record before 22:00:00 is one group. 9 is the maximum number inside, so we pick up 9. The maximum NRS score which the assessment time before 18:00:00 is 8. so the final score for hour 6 is 8
101-0011		20:00:00😁	9	O
101-0011	10	20:15:00😁	9
101-0011	12	22:05:00	7

My desire output:

subject	timepoint(hour)	score
101-0011	0	6
101-0011	1	6
101-0011	2	7	hour 2 has no record for medication, so just keep NRS score.
101-0011	4	8
101-0011	6	8
101-0011	10	9

currently I have this data. I need to find NRS score for each timepoint. the method is according to medication, for medication O(add 8 hour in A), I should add 4 hour downward from corresponding time of assessment, I pick up the the max NRS score in this time range. I still need to pick up a max NRS score up this timepoint, and then compare them. keep the smaller one as this timepoint final score. 

I spent two days and did not figure out how to do it. is there anyone can give me a help. Appreciate

[PaigeMiller]

Please show us the desired output.

Please explain further about what impact medication has on the analysis. What should we do about medication "A"? What if there are other medications?

Is the data really as you show it, with the first date and time assessment is 20Mar2023:0900:59.96 and the rest are just the time but no date?

[sophielr]

Thank you for getting back to me so quickly. I just need to find out score for different time point by sas. there are two medication, A and O, A has 4 hr time extension and O has 8 hr time extension. data is fine.

[PaigeMiller]

How about you go back to the spreadsheet shown in your original reply, and create a new column with the desired results, and show that to us?

[sophielr]

I modified my original post.

[PaigeMiller]

But this is not what I asked for, I want you to create a column in your original data table that has the desired result. I do not see any relationship between your first table and your second table, and can't make any use of your second table.

[sophielr]

sorry, I show the process with the first medication O at timepoint 1, the start assessment time is 11:21:00, then add 4 hr downward, so the last timepoint is 14:20:00, the maximum score is 8 in this range. the maximum score upward from 11:21:00 is 6. I pick up smaller one between 6 and 8 as my desired score. 

[Reeza]

Please provide data as text and more than one example as otherwise the solution provided will not meet your requirements. 

[sophielr]

Thank you. I update them in my first poster

[Reeza]

Does your timepoint variable have anything to do with your time points? Does it define the time periods?

[sophielr]

No, it is just a time point. hour 1/ hour 2, and so on.

[Reeza]

So the timepoint in the output has nothing to do with the time point variable in the input data set?

[sophielr]

the time point in raw data is the same as the hour in output form.

[Reeza]

You've called it "hour" in image and "timepoint" in text and I genuinely can't follow the logic with the data.

How is 1 mapped to 6, how is 4 mapped to 8?

Can you explain the calculation for each timepoint? And please clean up the data to show one input/output that have the appropriate variable name.

[sophielr]

sorry, I did not explain clearly. I did some modify to my first poster.