Inner Join based on word in two variables

[sameer112217]

I have two tables and want to have inner join. The issue is I want to join where even one word is common where application name is common variable

Table one

Application_Name

ETOL Pune INDIA

UPI Mumbai

ETOS India Ltd

CISCO

REDHAT

Infosys

Table two

Application_Name

ETOS

UPI

CISCO DELHI

IBM

SYNTEL

WIPRO

I want to  have inner join even if one word in both the tables are common with below desired output

UPI Mumbai

ETOS India Ltd

CISCO

I tried code

Proc SQL;

Create table want as

select a.application_name from one a inner join two b on

a.application_name contains b.application_name'

quit;

But if i use contains I might miss Cisco in table B which has more words. I am looking for a solution where it should have inner join even if one word in both tables application_name matches..

[smantha]

one way to do it

data two;
length app_name2  <samelength as application_name>;
set two;
if countc(application_name,' ') > 0 then do;
 app_name2=application_name
 do i = 1 to countc(application_name,' ')+1;
  application_name =strip(scan(app_name2,i,' '));
  output;
 end;
end;
else do;
output;
end;
run;
Proc SQL;

Create table want as

select a.application_name from one a inner join two b on

a.application_name contains b.application_name'

[Patrick]

If the match should always use the first word in the string then something like below could work.

scan(a.application_name,1) = scan(b.application_name,1)

[Ksharp · Accepted solution]

a.application_name contains STRIP( b.application_name )

or

b.application_name contains STRIP( a.application_name )