topic Re: identify unique observation per subject based on multiple conditions in SAS Programming

identify unique observation per subject based on multiple conditions

ari — Sun, 14 Aug 2016 21:17:15 GMT

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,

Re: identify unique observation per subject based on multiple conditions

Reeza — Sun, 14 Aug 2016 21:39:07 GMT

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;

Re: identify unique observation per subject based on multiple conditions

RW9 — Mon, 15 Aug 2016 09:15:11 GMT

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;

Re: identify unique observation per subject based on multiple conditions

ari — Mon, 15 Aug 2016 14:03:40 GMT

Thanks Reeza

Re: identify unique observation per subject based on multiple conditions

ari — Mon, 15 Aug 2016 14:04:19 GMT

Thanks RW9