Select subjects based on the combination of values

[Cruise]

Hi folks,

I'm trying to subset patients who takes combination of two specific values which means:

1. Select patients who takes both value '1 and 2' for "weight" variable such as ID=2: 4 in the data 'have'. Ignore patients who takes '1 or 2', or none such as ID=1: 3: 5.

2. If subjects tie on "weight" then take the row associated with the least value of 'diff_range' as shown in data want.

3. If data ties on diff_range then I can proc sort nodupkey by extra step, so don't worry about that.

The current SAS code keeps ID 1:3:5 in the 'want' data which I'm trying to eliminate.

Could you please help just to retrieve ID 2:4 in the final data as described step-wise above?

data have;
input id weight diff_range;
cards;
1	1	2
1	1	21
1	1	54
1	1	87
2	1	80
2	1	90
2	2	100
2	2	120
2	3	100
3	1	21
3	4	3
3	5	0
3	6	2
3	6	2
4	1	200
4	1	250
4	2	30
4	2	50
4	4	23
4	5	56
4	5	89
5	2	23
5	2	56
5	2	78
;
run;

data want;
input id weight diff_range;
cards;
2	1	80
2	2	100
4	1	200
4	2	30
;
run;

proc sql;
create table want(drop=_:) as
select *
from 
(select *, max(weight in (1,2)) as _c,min(diff_range) as _min from have group by id, weight)
group by id
having _c and _min=diff_range;
quit;
proc print; run; 

[novinosrin · Accepted solution]

@Cruise   enjoy

data have;
input id weight diff_range;
cards;
1	1	2
1	1	21
1	1	54
1	1	87
2	1	80
2	1	90
2	2	100
2	2	120
2	3	100
3	1	21
3	4	3
3	5	0
3	6	2
3	6	2
4	1	200
4	1	250
4	2	30
4	2	50
4	4	23
4	5	56
4	5	89
5	2	23
5	2	56
5	2	78
;
run;


proc sql;
create table  want as
select *
from
(select * from have where weight in (1,2) group by id
having sum(distinct weight)=3)
group by id, weight
having diff_range=min(diff_range)
order by id, weight;
quit;

[Cruise]

@novinosrin Thanks  a lot. Smart solution indeed.

[novinosrin]

Always welcome!