identify unique observation per subject based on multiple conditions

[ari]

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,

[Reeza · Accepted solution]

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;

[ari]

Thanks Reeza

[RW9]

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;

[ari]

Thanks RW9