left join on a partial string

Reply
Occasional Contributor
Posts: 16

left join on a partial string

I have table1 and table2

 

Trying to do a left join from Table1 to Table2

 

In table1 the key is Lamb

in table2 the key is Mary,had,a,Little,Lamb in one column

 

I want to join these tables on a key where table1 key matches the partial string in Table2. I want to bring back te results if Lamb is anywhere in the key.

 

Thanks,

 

paul

Grand Advisor
Posts: 10,239

Re: left join on a partial string

Really should provide some idea of the structure/contents of both tables and what the desired output may be.

 

Is something like this what your are looking for:

data table1;
   input word $;
datalines;
lamb
Lamb
sheep
cow
;
run;

data table2;
   key=  "Mary,had,a,Little,Lamb";output;
   key=  "Sheep are not cows";output;
run;

proc sql;
   select table1.*, table2.*
   from table1, table2
   where index(table2.key,strip(table1.word))>0;
quit;
Respected Advisor
Posts: 4,998

Re: left join on a partial string

Details will be important.  For example:

 

Does capitalization matter?

 

Partial words: 

 

Does this contain Lamb:  Lambaste,the,cowards

 

Does this contain sheep:  shy,sheepish,timid

 

Does this contain sheep:  wolf-in-sheep's-clothing

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