How to use the SAS retain statement to carry an observation forward

[UcheOkoro]

Please, I need help with using the SAS retain statement to carry the longest wait time forward for persons with same severity of illness. The severity of illness variable is the acuity (ordinal variable :1-5). Observations were grouped into time blocks and sorted by block and acuity. I also have a waiting time variable which I used to create the longest waiting time. I need to carry over the longest waiting time for persons with Same acuity.

Find below my SAS code

proc sort data= BLOCKEXAMPLE3; by block acuity descending waiting_time; run;
data blockexample4;
set blockexample3;
by block acuity ;
retain wait_longest ;
if first.acuity then do;
	wait_longest = waiting_time;
	wait_longest=wait_longest;
		end;
run;

[ballardw]

Is the need by ACUITY or the combination of Block and Acuity?

Provide some example data the desired output for that example so we have a chance of following the logic.

[UcheOkoro]

data  New;
input Obs  block Acuity waiting_time order_expected actual_order violation wait_longest;
datalines;
1 1 3 54 1 4 . 54
2 1 3 39 2 1 1 .
3 1 3 34 3 2 . .
4 1 4 69 4 3 . 69
;
run;

Observation 1,2,3 have the same acuity but the longest waiting time for the 3 was 54 minutes. I want to carry that over in the wait_longest column instead of having it recorded as missing. Thank you so much for your response.

[PaigeMiller · Accepted solution]

You can't RETAIN a variable that is already in the data set. You can only RETAIN a new variable that is created in the data set.

data blockexample4;
    set blockexample3;
    by block acuity;
    retain wait_longest1;
    if first.acuity then wait_longest1 = waiting_time;
    if not missing(wait_longest) then wait_longest1=wait_longest;
run;

[UcheOkoro]

Thank you so much for your assistance.

[sbxkoenk]

proc sort data=work.BLOCKEXAMPLE3; 
 by block acuity descending waiting_time; 
run;

data work.BLOCKEXAMPLE4;
 set work.BLOCKEXAMPLE3;
 retain rownumber .;
 by block acuity descending waiting_time; 
 if first.acuity then do; rownumber=1; end;
 rownumber = rownumber + 1;
run;

proc expand data=work.BLOCKEXAMPLE4
            out =work.BLOCKEXAMPLE4_exp
            to=day
          /*plots=(input output)*/ 
            method=STEP EXTRAPOLATE;
  by block acuity;
  id rownumber;
  convert waiting_time;
run;
/* end of program */

Koen