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..
a.application_name contains STRIP( b.application_name )
or
b.application_name contains STRIP( a.application_name )
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'
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)
a.application_name contains STRIP( b.application_name )
or
b.application_name contains STRIP( a.application_name )
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.