topic Re: How to find two variables that exist in the same group only in SAS Programming

How to find two variables that exist in the same group only

sashelp123 — Wed, 17 Aug 2022 17:39:52 GMT

Hello,

I am looking for how to find two different variables but only if they exist within the same group. For example I want a list of groups where both A and B exist together. In the example below, that would be Group 1, Group 3, and Group 4. Thank you!

Group	Code
1	C
2	B
3	B
4	B
5	C
3	A
1	B
4	A
5	B
1	B

Re: How to find two variables that exist in the same group only

PaigeMiller — Wed, 17 Aug 2022 18:05:25 GMT

@sashelp123 wrote:

I am looking for how to find two different variables but only if they exist within the same group.

Do you mean you want two observations if two different values of CODE exists in the same group?

For example I want a list of groups where both A and B exist together. In the example below, that would be Group 1, Group 3, and Group 4.

I don't see how Group 1 meets this criterion.

Show us the desired output

Re: How to find two variables that exist in the same group only

sashelp123 — Wed, 17 Aug 2022 18:26:38 GMT

Hi Paige,

Thanks, you are correct. I mean observations, and my desired output would be Group 3, and Group 4, have both A and B in the same group (not group 1).

Re: How to find two variables that exist in the same group only

PaigeMiller — Wed, 17 Aug 2022 19:06:46 GMT

So is that the entire question, A and B in the same group?

Or is the question really like this:

What groups have both A and B; and what groups have both A and C, and so on?

Re: How to find two variables that exist in the same group only

sashelp123 — Wed, 17 Aug 2022 19:14:37 GMT

Yep that's the entire question, how to find what groups have both A and B?

Re: How to find two variables that exist in the same group only

PaigeMiller — Wed, 17 Aug 2022 19:34:40 GMT

UNTESTED CODE

proc sort data=have;
    by group;
run;
proc transpose data=have out=have_t;
    by group;
    id code;
run;
data want;
    set have_t;
    if whichc('A',of col:)>0 and whichc('B',of col:)>0;
run;

If you want tested code, please provide the data as SAS data step code (instructions) and not as screen captures.

Re: How to find two variables that exist in the same group only

andreas_lds — Thu, 18 Aug 2022 04:58:58 GMT

Untested:

proc sort data=have out=sorted nodupkey;
   by group code;
run;

data want;
   set sorted;
   by group;
   
   if not first.group and code = 'B' and lag(code) = 'A';
   
   keep group;
run;

EDIT: Simplified the code.

Re: How to find two variables that exist in the same group only

Kurt_Bremser — Thu, 18 Aug 2022 06:52:03 GMT

proc sort data=have;
by group;
run;

data want;
do until (last.group);
  set have;
  by group;
  if code = "A" then _a = 1;
  if code = "B" then _b = 1;
end;
if _a and _b;
keep group;
run;

Re: How to find two variables that exist in the same group only

Ksharp — Thu, 18 Aug 2022 12:33:58 GMT

data have;
input Group	Code $;
cards;
1	C
2	B
3	B
4	B
5	C
3	A
1	B
4	A
5	B
1	B
;

proc sql;
create table want as
select * from have
 group by group
  having sum(code='A') ne 0 and sum(code='B') ne 0;
quit;