topic Re: How to find if there's a match between 2 columns with group by in SAS Programming

How to find if there's a match between 2 columns with group by

siare1023 — Fri, 08 Jul 2022 15:09:34 GMT

Hello, I need help finding a way to identify if there's a match between 2 columns with a group by criteria. For example

data tempp;
length first last col_a col_b $10;
input first last col_a col_b;
datalines;
jone smith aa bb
jone smith cc bb
jone smith dd cc
jone smith ee aa
peter paul zz dd
peter paul pp dd
peter paul aa cc
peter paul bb aa
;
run;

We see that for jone smith, there's a matching 'aa' in col_a and col_b, same for peter paul. Is there a way to search within a group (here it's by person) and see if there's a match between 2 columns?

Thanks in advance for any help!

Re: How to find if there's a match between 2 columns with group by

maguiremq — Fri, 08 Jul 2022 16:21:26 GMT

There's also a second one for Jone?

data tempp;
length first last col_a col_b $10;
input first last col_a col_b;
datalines;
jone smith aa bb
jone smith cc bb
jone smith dd cc
jone smith ee aa
peter paul zz dd
peter paul pp dd
peter paul aa cc
peter paul bb aa
;
run;

proc sql;
	create table 	want as
		select
					t1.first,
					t1.last,
					t1.col_a,
					t2.col_b
		from
					tempp as t1
						inner join
					tempp as t2
							on 	t1.first = t2.first 	and
								t1.last = t2.last		and
								t1.col_a = t2.col_b;
quit;

Is that what you wanted?

Re: How to find if there's a match between 2 columns with group by

siare1023 — Fri, 08 Jul 2022 18:15:02 GMT

Wow I didn't even consider inner joining with itself... I can definitely work with this, thank you so much!! 🙂

Re: How to find if there's a match between 2 columns with group by

PeterClemmensen — Fri, 08 Jul 2022 18:22:02 GMT

Alternatively

data tempp;
length first last col_a col_b $10;
input first last col_a col_b;
datalines;
jone smith aa bb
jone smith cc bb
jone smith dd cc
jone smith ee aa
peter paul zz dd
peter paul pp dd
peter paul aa cc
peter paul bb aa
;
run;

data want;

   if _N_ = 1 then do;
      dcl hash h(dataset : "tempp", multidata : 'Y');
      h.definekey('first', 'last', 'col_b');
      h.definedone();
   end;

   set tempp;
   by first last;

   if h.check(key : first, key : last, key : col_a) = 0;

run;

Result

first  last   col_a  col_b
jone   smith  aa     bb
jone   smith  cc     bb
peter  paul   aa     cc