topic extract observations have same value from different rows in two columns in SAS Data Management

extract observations have same value from different rows in two columns

echoli — Fri, 07 Apr 2017 01:16:38 GMT

Hi All,

What should I do if I want to extract all the observations have same value but may in different two rows in two columns. For example:

Col_1 Col_2

1 1

2 2

3 3

4 5

5 6

6 7

7 9

8 10

The red color records are observations I want to extract. I am using SAS 9.4.

Any idea?

Thanks,

Re: extract observations have same value from different rows in two columns

Astounding — Fri, 07 Apr 2017 03:21:07 GMT

No promises (in fact, not even tested), but this might work:

proc sql;

create table want as select col_1, col_2 from have

where col_1 in (select distinct col_2 from have)

or col_2 in (select distinct col_1 from have);

quit;

Re: extract observations have same value from different rows in two columns

Ksharp — Fri, 07 Apr 2017 13:21:59 GMT

data have;
input Col_1      Col_2;
cards;
1              1
2              2
3              3
4              5
5              6
6              7
7              9
8              10
;
run;

data want;
 if _n_=1 then do;
  if 0 then set have;
  declare hash h1(dataset:'have');
  h1.definekey(key:'col_1');
  h1.definedone();
  
  declare hash h2(dataset:'have');
  h2.definekey(key:'col_2');
  h2.definedone();
 end;
set have;
if h1.check(key:col_2)=0 or h2.check(key:col_1)=0;
run;