identify unique observation per subject based on multiple conditions

Solved
Frequent Contributor
Posts: 106

identify unique observation per subject based on multiple conditions

Have:

 id value 1 0 1 1 1 2 2 0 2 0 2 2 2 0 3 0 3 0 3 0 3 0

want:

 id value 1 1 2 2 3 0

I need to select one observation per subject. if the value is 1 then retain that observation. if not 1 then look for value 2 and retain it. if both 1 and 2  are not there then look for 0 and retian it.

I appreciate any help to get the output.

Thanks,

Accepted Solutions
Solution
‎08-15-2016 10:04 AM
Super User
Posts: 23,262

Re: identify unique observation per subject based on multiple conditions

You can add in a custom sort variable, sort the variable and then take the first/last depending on your logic. Here's a way using proc format.

``````data have;
input id value;
value_order=put(value, order_fmt. -l);
cards;
1 0
1 1
1 2
2 0
2 0
2 2
2 0
3 0
3 0
3 0
3 0
;

proc format ;
value order_fmt 1=1 2=2 0=3;
run;

proc sort data=have;
by id value_order;
run;

data want;
set have;
by id;

if first.id;
run;``````

All Replies
Solution
‎08-15-2016 10:04 AM
Super User
Posts: 23,262

Re: identify unique observation per subject based on multiple conditions

You can add in a custom sort variable, sort the variable and then take the first/last depending on your logic. Here's a way using proc format.

``````data have;
input id value;
value_order=put(value, order_fmt. -l);
cards;
1 0
1 1
1 2
2 0
2 0
2 2
2 0
3 0
3 0
3 0
3 0
;

proc format ;
value order_fmt 1=1 2=2 0=3;
run;

proc sort data=have;
by id value_order;
run;

data want;
set have;
by id;

if first.id;
run;``````
Frequent Contributor
Posts: 106

Thanks Reeza

Super User
Posts: 9,414

Re: identify unique observation per subject based on multiple conditions

Hi,

```proc sql;
create table WANT as
select  distinct A.ID,
case  when exists(select distinct ID from HAVE where ID=A.ID and VALUE=1) then 1
when exists(select distinct ID from HAVE where ID=A.ID and VALUE=2) then 2
else 0 end as VALUE
from    HAVE A;
quit;```
Frequent Contributor
Posts: 106

Re: identify unique observation per subject based on multiple conditions

Thanks RW9

☑ This topic is solved.