SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

extract observations have same value from different rows in two columns

Reply
Frequent Contributor
Posts: 76

extract observations have same value from different rows in two columns

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,

C

 

Super User
Posts: 5,082

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

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;

Super User
Posts: 9,681

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

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;

Ask a Question
Discussion stats
  • 2 replies
  • 185 views
  • 2 likes
  • 3 in conversation