topic Inner Join based on word in two variables in SAS Programming

Inner Join based on word in two variables

sameer112217 — Mon, 06 Jul 2020 21:32:11 GMT

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..

Re: Inner Join based on word in two variables

smantha — Mon, 06 Jul 2020 23:00:00 GMT

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'

Re: Inner Join based on word in two variables

Patrick — Tue, 07 Jul 2020 01:31:30 GMT

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)

Re: Inner Join based on word in two variables

Ksharp — Tue, 07 Jul 2020 11:51:10 GMT

a.application_name contains STRIP( b.application_name )

b.application_name contains STRIP( a.application_name )