Merge data sets without exact match but with LIKE match

[Ronein]

Hello

I want to merge data set aaa and data set bbb  .

I want to check if any value of column Team in data set aaa exists in data set bbb.

The way of checking is:

step1- delete blanks (before,between and after ) in column Team in data seta aaa 

step2- delete blanks (before,between and after ) in column Team in data seta bbb

Step3- check if calculated  Team column in data set aaa  exists in data set b

What is the way to do it please?

Data aaa;
input  team $ 1-20;
cards;
London
Tel aviv
Berlin
Warsaw
Tokyo
;
Run;

Data bbb;
input  team $ 1-20;
cards;
Inc London city
Telaviv
Western Berlin 
;
Run;

 
data aaa_new;
set aaa;
team_new=COMPRESS(team);
drop team;
rename team_new=team;
run;


data bbb_new;
set bbb;
team_new=COMPRESS(team);
drop team;
rename team_new=team;
run;

[ChrisNZ]

Should  Berlin  match  Western Berlin  ?

[Ronein]

Yes, because name Berlin included in name West Berlin

[andreas_lds]

Please use the search-function, partial matching has been discussed many times, you should be able to adapt one of the solutions.

[gamotte]

Hello,

You can try as follows :

Data aaa;
input  team $ 1-20;
cards;
London
Tel aviv
Berlin
Warsaw
Tokyo
;
Run;

Data bbb;
input  team $ 1-20;
cards;
Inc London city
Telaviv
Western Berlin 
;
Run;

proc sql noprint;
  CREATE TABLE want AS
  SELECT a.team, b.team AS team_b
  FROM aaa a
  LEFT JOIN bbb b
  ON compress(b.team) CONTAINS compress(a.team)
  ;
quit;

[ChrisNZ]

Or, to have more matches:

proc sql ;
  CREATE TABLE want AS
  SELECT unique a.team, b.team AS team_b
  FROM      aaa a
  LEFT JOIN bbb b
  ON find(compress(b.team), compress(a.team), 'i')
   | find(compress(a.team), compress(b.team), 'i') ;
quit;