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;
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
;
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
;
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;
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
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.