Help using Base SAS procedures

Find same value in two columns

Reply
Contributor
Posts: 35

Find same value in two columns

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;

Super Contributor
Posts: 1,636

Re: Find same value in two columns

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

Contributor
Posts: 35

Re: Find same value in two columns

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

Respected Advisor
Posts: 3,124

Re: Find same value in two columns

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

Respected Advisor
Posts: 3,124

Re: Find same value in two columns

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

Super Contributor
Posts: 1,636

Re: Find same value in two columns

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

Respected Advisor
Posts: 3,124

Re: Find same value in two columns

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

Haikuo

Respected Advisor
Posts: 4,660

Re: Find same value in two columns

OK I'll bite. Here is another solution.

proc sql;

select value1 from findsame

intersect

select value2 from findsame;

PG

PG
Respected Advisor
Posts: 3,124

Re: Find same value in two columns

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

Regular Contributor
Posts: 222

Re: Find same value in two columns

All these are awesome!

Ask a Question
Discussion stats
  • 9 replies
  • 1401 views
  • 3 likes
  • 5 in conversation