identify unique observation per subject based on multiple conditions

Accepted Solution Solved
Reply
Frequent Contributor
Frequent Contributor
Posts: 103
Accepted Solution

identify unique observation per subject based on multiple conditions

Have:

idvalue
10
11
12
20
20
22
20
30
30
30
30

 

want:

 

id value
11
22
30
  

 

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: 19,167

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;

View solution in original post


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

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
Frequent Contributor
Posts: 103

Re: identify unique observation per subject based on multiple conditions

Thanks Reeza

Super User
Super User
Posts: 7,720

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
Frequent Contributor
Posts: 103

Re: identify unique observation per subject based on multiple conditions

Thanks RW9 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 395 views
  • 0 likes
  • 3 in conversation