topic the highest result needs to be selected if ID has more than one value in SAS Programming

the highest result needs to be selected if ID has more than one value

anandrc — Wed, 19 Nov 2025 11:29:57 GMT

Hi Experts,

Appreciate your input.

I have data like below.
It has ID and their PDL1 values.

Data have;
Input ID $8. PDL1 $8.;
cards;
E0401004 0
E0401004 <1
E0402001 <1
E0402001 80
E0403018 70
E0403018 80
E0403018 95
E0403503 N/A
E0403503 0
E1005513 12
E1005513 5
;
Run;

We need to add priority variable as follows.

if PDL1 = "N/A" then priority=0;
if PDL1 in ('0', '<1') then priority=1;
if 1<=PDL1<=49 then priority=2;
if 50<=PDL1 then priority=3;

If a patient has more than one result, the highest result needs to be selected.

I want

ID	Expected PDL1	Priority
E0401004	0	1
E0402001	80	3
E0403018	95	3
E0403503	0	1
E1005513	12	2

Re: the highest result needs to be selected if ID has more than one value

LinusH — Wed, 19 Nov 2025 11:45:33 GMT

Once your prio logic is in place, you can use SQL, something like:

proc sql;
   select id, pdl1, priority
      from have
      group by id
      having max(priority) = priority
;
quit;

Re: the highest result needs to be selected if ID has more than one value

andreas_lds — Wed, 19 Nov 2025 11:55:36 GMT

Easy to solve after PDL1 has been converted to a numeric variable.

proc format;
    invalue pdl2num
        "N/A" = .
        "<1" = 0
        other = [best.]
    ;
    value pdlprio
        . = 0
        0 = 1
        1 - 49 = 3
        50 - HIGH = 4
    ;
run;

data fixed;
    set have(rename= (pdl1 = pdl1_str));

    pdl1 = input(pdl1_str, pdl2num.);
    priority = put(pdl1, pdlprio.);
run;

Use the code posted by @LinusH to get the final result.

Re: the highest result needs to be selected if ID has more than one value

anandrc — Wed, 19 Nov 2025 13:30:56 GMT

Thanks for the reply.

Expected PDL1 variable result - only when there is more than one value of any combination (<1, 0) the results should display <1 and not 0.
Ideally per logic, its 0 because that higher than <1 but our report need to show as <1.

Sorry for the confusion.
So want the final dataset to show

ID Expected PDL1
E0401004 <1
E0402001 80
E0403018 95
E0403503 0
E1005513 12