BookmarkSubscribeRSS Feed
George_S
Fluorite | Level 6

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;

9 REPLIES 9
Linlin
Lapis Lazuli | Level 10

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
Fluorite | Level 6

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

Haikuo
Onyx | Level 15

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
Onyx | Level 15

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
Lapis Lazuli | Level 10

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
Onyx | Level 15

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

Haikuo

PGStats
Opal | Level 21

OK I'll bite. Here is another solution.

proc sql;

select value1 from findsame

intersect

select value2 from findsame;

PG

PG
Haikuo
Onyx | Level 15

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
Fluorite | Level 6

All these are awesome!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 5002 views
  • 3 likes
  • 5 in conversation