Help with selecting only 2 values in a variable

[Kalai2008]

Hi

Given Dataset:

Id Code

1 a

1 b

1 c

1 d

2 a

2 c

3 a

3 c

3 d

4 a

4 b

5 a

5 c

Data Want:

Id

2

5

I only need the (a,c) pair as output. But I am getting different Output.

proc sql;

create table want as

select *

case when code in ('a','c') then 1 end as check

from have where code in ('a','c');run;

[Tom]

Your output looks nothing like what your code (once typos are fixed) would produce.
What is the criteria for picking ID=2 and ID=5? Just have a or c (or even having both) would not result in just 2 and 5 from your example input.

[Kalai2008]

Thanks for checking. My Criteria is to pick the ID's that have only this pair "a' and "c". ID 2 and 5 have the values "a" and "c".

[Tom · Accepted solution]

So if they have anything else they are off the list?

 select id from have
 group by id
 having min(code in ('a','c'))=1
 ;

Or do they also have to have both A and C?

proc sql ;
 select id from have
 group by id
 having min(code in ('a','c'))=1
   and count(distinct code)=2
 ;

[Kalai2008]

Tom, Thank you. the second part of the code worked good. Appreciate it.

[novinosrin]

data have;
input Id Code $;
cards;

1 a

1 b

1 c

1 d

2 a

2 c

3 a

3 c

3 d

4 a

4 b

5 a

5 c
;

proc sql;
create table want as
select *
from have
group by id
having count(distinct code)=2 and sum(code in ('a','c'))=2;
quit;

[Kalai2008]

Thank you, but it is not working good with my real data.

[novinosrin]

That means you really need to provide a better an representative sample. A Mock sample is fine, but has to be a representative sample

for instance, your values might be of mixed cases, or your values might have leading blanks and so forth

[Stephen_c_tran]

Another query:

proc sql;
create table want as
select  id
       ,count(*) as count_id
       ,sum(case when code = 'a' or code = 'c' then 1
                 else 0 end) as count_a_c
from have
group by id
having calculated count_id = calculated count_a_c
quit;

[Kalai2008]

Thank you so much.. I will try this one.