topic Merge data sets without exact match but with LIKE match in SAS Programming

Merge data sets without exact match but with LIKE match

Ronein — Thu, 16 Jul 2020 08:15:56 GMT

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;

Re: Merge data sets without exact match but with LIKE match

ChrisNZ — Thu, 16 Jul 2020 08:28:08 GMT

Should Berlin match Western Berlin ?

Re: Merge data sets without exact match but with LIKE match

andreas_lds — Thu, 16 Jul 2020 08:52:04 GMT

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

Re: Merge data sets without exact match but with LIKE match

Ronein — Thu, 16 Jul 2020 09:16:05 GMT

Yes, because name Berlin included in name West Berlin

Re: Merge data sets without exact match but with LIKE match

gamotte — Thu, 16 Jul 2020 09:33:08 GMT

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;

Re: Merge data sets without exact match but with LIKE match

ChrisNZ — Thu, 16 Jul 2020 10:04:35 GMT

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;