topic Re: Help with selecting only 2 values in a variable in SAS Programming

Help with selecting only 2 values in a variable

Kalai2008 — Sun, 29 Mar 2020 23:36:16 GMT

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:

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;

Re: Help with selecting only 2 values in a variable

Tom — Sun, 29 Mar 2020 23:44:28 GMT

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.

Re: Help with selecting only 2 values in a variable

Kalai2008 — Sun, 29 Mar 2020 23:49:20 GMT

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".

Re: Help with selecting only 2 values in a variable

novinosrin — Sun, 29 Mar 2020 23:53:26 GMT


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;

Re: Help with selecting only 2 values in a variable

Tom — Mon, 30 Mar 2020 00:04:52 GMT

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
 ;

Re: Help with selecting only 2 values in a variable

Kalai2008 — Mon, 30 Mar 2020 00:08:26 GMT

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

Re: Help with selecting only 2 values in a variable

Kalai2008 — Mon, 30 Mar 2020 00:14:51 GMT

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

Re: Help with selecting only 2 values in a variable

novinosrin — Mon, 30 Mar 2020 00:15:03 GMT

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

Re: Help with selecting only 2 values in a variable

Stephen_c_tran — Mon, 30 Mar 2020 19:44:39 GMT

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;

Re: Help with selecting only 2 values in a variable

Kalai2008 — Thu, 02 Apr 2020 01:13:32 GMT

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