find maximum value from previous rows

[sophielr]

Thank you for any help for my question. I have to get red number. '6' is the maximum value which time of assessment recorded before 11:21:00. '8' is the maximum value which time of assessment recorded before 13:59:59. I do not know how to get '6' and '8' by SAS.

ubject	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	6
101-0011	2	12:13:00	7
101-0011	4	13:59:59	6	O	8
101-0011	4	14:20:00	8
101_0011	6	16:10:33	9

[yabwon · Accepted solution]

I would said: "Take the solution from yesterday and put some effort..."

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-1;

  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;

[sophielr]

Appreciate, @yabwon . You help me again.

[Tom]

I don't follow your description, but your data makes it look like you want the maximum score BEFORE the observation that has MEDICATION='O'.  So just use a retained variable to capture the maximum score.

data have;
  input Subject $ hour time :time. score medication $ want ;
  format time tod8. ;
cards;
101-0011 0 09:00:59 6 . . 
101-0011 1 11:13:00 3 . .  
101-0011 1 11:21:00 8 O 6
101-0011 2 12:13:00 7 . .  
101-0011 4 13:59:59 6 O 8
101-0011 4 14:20:00 8 . .   
101_0011 6 16:10:33 9 . .
;

data want ;
  set have;
  by subject ;
  if first.subject then max=.;
  retain max;
  if medication='O' then nrs=max;
  max=max(max,score);
run;

OBS    Subject     hour        time    score    medication    want    max    nrs

 1     101-0011      0     09:00:59      6                      .      6      .
 2     101-0011      1     11:13:00      3                      .      6      .
 3     101-0011      1     11:21:00      8          O           6      8      6
 4     101-0011      2     12:13:00      7                      .      8      .
 5     101-0011      4     13:59:59      6          O           8      8      8
 6     101-0011      4     14:20:00      8                      .      8      .
 7     101_0011      6     16:10:33      9                      .      9      .

[yabwon]

@Tom ,

I based my solution on the one I gave for related question in this thread: https://communities.sas.com/t5/SAS-Programming/calculate-time-difference-in-one-column/td-p/871588

but I want to say that if it's about today question I like yours much more!

Bart

[sophielr]

Thank you, Tom. You show me a new method.