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
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.));
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.));
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").
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.