CODEWITHSUNDEEP
sqlsasstring-matching
Donald William Glossfield
Donald William Glossfield

Reputation: 409

Finding observations partially matching a field from another dataset

The case is the following: I have two datasets. From the first one, I need to find every observation containing in field any one number found in field2 from the second dataset.

First data set:

field
-----
dsggagfa 837 dgfsdg
879 thh
gasgg 7999 ghth

Second data set:

field2
------
837
879

Expected results:

field
-----
879 thh
fegfrsg 879 thh

I am looking for something like this:

select * 
  from firstdata 
 where field like in (select field2 from seconddata);

 or

select * 
from firstdata 
where field in %"select field2 from seconddata"%;

Upvotes: 1

Views: 63

Answers (2)

Dominic Comtois
Dominic Comtois

Reputation: 10401

This will work, given your field2 from table 2 is numeric. Also, using prxmatch with \b's (word boundaries), assures you don't select erroneously, for instance, "gasgg 7999 ghth" if your table 2 contains number 799 (or 999, 79, 99).

data t1;
  input field $32.;
  datalines;
dsggagfa 837 dgfsdg
879 thh
gasgg 7999 ghth
;

data t2;
  input field2 8.;
  datalines;
837
879
;

proc sql;
  select t1.field
    from t1, t2
   where prxmatch(cats("/\b",strip(put(t2.field2, 8.)),"\b/"), t1.field);
quit;

Results

field
-----
dsggagfa 837 dgfsdg
879 thh

Upvotes: 2

Jetzler
Jetzler

Reputation: 797

Assuming field2 is a string this will work. index will return the position of field2 in field. The where condition is TRUE when field2 is found in field.

select a.* 
       from firstdata a, seconddata b
          where index(a.field, b.field2)
       ;

Upvotes: 1

Related Questions