Obtaining best response from multiple records.

[RAVI2000]

I have a dataset with multiple treatment response records. I have a order of best response which is (5, 1, 7, 2, 3, 6). I want to output the best response from the multiple records which ever comes first

data data;
input @1 SEQNO $6. @8 SEGMENT $16. @25 TreatmentResponse 1.;
cards;
May001 Treatment        .
M001   Treatment        1
M001                    1
M002   Progression       6
M012   Treatment        3
M012   Treatment        6
M012   End of Treatment .
M015   Treatment        6
M017   Treatment        7
M017   Treatment        5
M017   Treatment        1
M018   Treatment        1
M018   Treatment        5
M020   Treatment        2
M020   Treatment        6
M021   Treatment        7
M022   Treatment        2
;
run;

I want output single record per subject with best response (order):

May001 Treatment        .
M001   Treatment        1
M002   Progression       6
M012   Treatment        3
M015   Treatment        6
M017   Treatment        5
M018   Treatment        5
M020   Treatment        6
M021   Treatment        7
M022   Treatment        2

[ChrisNZ · Accepted solution]

Like this?

proc format; 
   value order 5='5' 1='4' 7='3' 2='2' 3='1' 6='0' ;

proc sql;
  select *
  from HAVE
  group by SEQNO
  having put(TREATMENTRESPONSE,order.)=max(put(TREATMENTRESPONSE,order.));

[ballardw]

You request "per subject". Which variable(s) define a specific subject?

My basic approach would be to create a new variable where "best" is the greatest value, ie your current 5 is mapped to 10(or similar) and the "worst" non-missing is mapped to 1. The use a procedure like Proc means/summary or if you only need a report then Proc Report or Tabulate.  to identify the maximum value by subject. Create a format that will display the 10 back to 5 (if that is the "best").

[Rydhm]

Create a separate variable with its order and then sort it. Keep the first entry.

data have;
input @1 SEQNO $6. @8 SEGMENT $16. @25 TreatmentResponse 1.;
cards;
May001 Treatment        .
M001   Treatment        1
M001                    1
M002   Progression      6
M012   Treatment        3
M012   Treatment        6
M012   End of Treatment .
M015   Treatment        6
M017   Treatment        7
M017   Treatment        5
M017   Treatment        1
M018   Treatment        1
M018   Treatment        5
M020   Treatment        2
M020   Treatment        6
M021   Treatment        7
M022   Treatment        2
;
run;

data have1;
 set have;
 if TreatmentResponse=5 then temp=1;
 else if TreatmentResponse=1 then temp=2; 
 else if TreatmentResponse=7 then temp=3; 
 else if TreatmentResponse=2 then temp=4; 
 else if TreatmentResponse=3 then temp=5; 
 else if TreatmentResponse=6 then temp=6;
 else if TreatmentResponse=. then temp=99;
run;

proc sort data=have1; by SEQNO temp; run;

data output;
 set have1;
  by seqno;
  if first.seqno;
  drop temp;
run;

proc print data=output; run;