join on condition contain specific string

[ger15xxhcker]

Hello!

I I'd like to join 2 table (have1 left join have2) if the table1.string contains table2.string2 and id1=id2;

How can i detect that contain (or do not contain) character variables. I 'd like to apply in the "on" condition in proc sql statement.

data  infile cards truncover;
  input @;
  _infile_=tranwrd(_infile_,'NULL',.);
  input id string $200.;
  cards;
1   ('1B' '1C' '1D' '2L' '2K' '1G' '1H' '11' 'AA')
2   ('2B' '3C' '4D' '4L' '2K' '4G' '0H')
3   ('7B' '9C' '8D' '2L' '2K' '1G' '1H' '45')
4   ('1B' '1C' '1D' '3L' '2K' '0G' '0H')
;
RUN;

data have2;
 input id2 string2 $2.;
 datalines;
1 4F
2 2B
3 7D
4 1C
 ;
run;

data want;
 input id3 string3 $2.;
 datalines;
1 
2 2B
3 
4 1C
 ;
run;

 Thanks!

[gamotte]

Hello,

You can use the findw function

proc sql;
CREATE TABLE WANT AS
SELECT a.*, b.*
FROM have1 a
LEFT JOIN have2 b
ON b.ID2=a.ID and findw(a.String, b.string2," '");
quit;

[andreas_lds]

Why proc sql? The problem can be solved by a data step:

data want;
   merge have1 have2(rename=(id2=id));
   by id;

   length string3 $2;

   string3 = ifc(index(string, string2), string2, ' ');

   drop string string2;
run;

[Patrick · Accepted solution]

Here two more syntax options.

proc sql;
/*  CREATE TABLE WANT AS*/
    SELECT a.*, b.*
      FROM have1 a
        LEFT JOIN have2 b
          ON b.ID2=a.ID and a.string contains b.string2
  ;
quit;

proc sql;
/*  CREATE TABLE WANT AS*/
    SELECT a.*, b.*
      FROM have1 a
        LEFT JOIN have2 b
          ON b.ID2=a.ID and a.string ? b.string2
  ;
quit;