topic Re: left join on a partial string in SAS Programming

left join on a partial string

paul2877 — Wed, 18 Jan 2017 17:50:06 GMT

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

Re: left join on a partial string

ballardw — Wed, 18 Jan 2017 18:15:34 GMT

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;

Re: left join on a partial string

Astounding — Wed, 18 Jan 2017 19:01:26 GMT

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