Partial match merging

[dac_js]

I have two tables:

data A;
length var2 $ 50;
input
var1 var2 $ &;
datalines;
1 zzzblue sky
2 BlUe sky
3 green leaf
4 red apple
5 red apple
;
run;

data B;
length var4 $ 50;
input
var3 var4 $ &;
datalines;
14545 BLUE
26464 blue
34646 gReen
;
run;

Var4 in dataset B matches partially with var2 in dataset A. I need help with a many to many merge based on this partial match where the new dataset will look like this-

var1	var2	var3	var4
1	zzzblue sky	14545	BLUE
2	BlUe sky	26464	blue
3	green leaf	34646	gReen

[ballardw]

You don't show a "many to many" merge, or join.

You seem to have some additional restriction, not stated, as to why you do not have a result like:

zzzblue sky

26464

blue

So you need to provide the other rules involved.

[dac_js]

Thanks! I missed the additional restriction.

[Patrick · Accepted solution]

Your description of the logic needs some additional specification to be clear. 

Does one of below two SQLs return what you're after?

proc sql;
  select 
    a.*,
    b.var4
  from 
    a
    inner join
    b
      on find(a.var2,b.var4,'it')>0
  ;
quit;


proc sql;
  select 
    a.*,
    b.var4
  from 
    a
    inner join
    (select distinct upcase(b.var4) as var4 from b) as b
      on find(a.var2,b.var4,'it')>0
  ;
quit;

Please note that even with the 2nd SQL if there is more than one matching substring in table A (like: Bluegreen water) then you would still get into a many:many situation. Is that what you want? If not what should happen in such a case? (please amend your sample data with such a case and show us the desired result).

[andreas_lds]

You have to explain in detail the rules to be applied.

The first two obs of both datasets match, so why is only one match expected to be written to the result dataset?

[dac_js]

Thanks! I missed the additional specification.