topic Re: Find same value in two columns in SAS Procedures

Find same value in two columns

George_S — Wed, 25 Apr 2012 17:04:50 GMT

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;

Re: Find same value in two columns

Linlin — Wed, 25 Apr 2012 17:15:37 GMT

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

Re: Find same value in two columns

George_S — Wed, 25 Apr 2012 17:46:38 GMT

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

Re: Find same value in two columns

Haikuo — Wed, 25 Apr 2012 17:54:13 GMT

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

Re: Find same value in two columns

Haikuo — Wed, 25 Apr 2012 18:00:44 GMT

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

Re: Find same value in two columns

Linlin — Wed, 25 Apr 2012 18:06:10 GMT

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

Re: Find same value in two columns

Haikuo — Wed, 25 Apr 2012 18:14:13 GMT

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

Haikuo

Re: Find same value in two columns

PGStats — Wed, 25 Apr 2012 18:28:55 GMT

OK I'll bite. Here is another solution.

proc sql;

select value1 from findsame

intersect

select value2 from findsame;

Re: Find same value in two columns

Haikuo — Wed, 25 Apr 2012 18:41:51 GMT

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

Re: Find same value in two columns

Mike_Davis — Fri, 27 Apr 2012 18:17:38 GMT

All these are awesome!