Find same value in two columns

[George_S]

Hello everyone,

Here is the part of a dataset,
I want to check whether there was any same value in the two column.
please help.

Thanks!

data findsame;
input value1  value2;
cards;
1998    1540
2727    222222
111111  206415
207206    207322
207216   207884
207319    208778
208490    209184
208628    111111
222222   209696
208924    209719
;
run;

[Linlin]

Hi George,

Try this:

proc sql;

create table want as select a.value1 from findsame a

   where a.value1 in (select b.value2 from findsame b);

quit;

proc print;run;

Linlin

[George_S]

Could this be done in data step or other special procedure?

[Haikuo]

Since you asked, here is a data step hash approach:

data want (keep=value2);

  if _n_=1 then do;

  if 0 then set findsame;

dcl hash h(dataset: 'findsame');

h.definekey('value2');

h.definedone();

end;

set findsame (keep=value1 rename=value1=value2);

rc=h.find();

if rc=0 then output;

run;

Regards,

Haikuo

[Haikuo]

Here is another data step approach:

data want;

set findsame(keep=value1) ;

do _n_=1 to nobs;

set findsame(keep=value2) nobs=nobs point=_n_;

  if value1=value2 then output;

  end;

run;

BTW, there could be SQL join approach besides Linlin's SQL subquery solution:

proc sql;

select * from findsame a, findsame b

where a.value1=b.value2;

quit;

Regards,

Haikuo

[Linlin]

Hi Haikuo,

take a look of my code:

data findsame;

input value1  value2;

cards;

1998    1540

2727    222222

111111  206415

207206    207322

207216   207884

207319    208778

208490    209184

208628    111111

222222   209696

208924    209719

;

run;

data want;

  set findsame(keep=value1);

   do i=1 to nobs;

      set findsame(keep=value2)  point=i nobs=nobs;

   if value1=value2 then output;

   end;

run;

proc print;run;

Linlin

[Haikuo]

LOL, oh, yeah, apparently we were from the same farm.

Haikuo

[PGStats]

OK I'll bite. Here is another solution.

proc sql;

select value1 from findsame

intersect

select value2 from findsame;

PG

[Haikuo]

Nice! Data step, data step plus Hash, SQL subquery, SQL join and Now thanks to PG, we have SQL union. SAS does have a deeeep bench.

Haikuo

[Mike_Davis]

All these are awesome!