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

[anandrc]

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

[LinusH · Accepted solution]

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;

[andreas_lds]

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.

[anandrc]

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