joining 2 datasets fuzzy matching

[DaveLarge]

hi all,

im sure i done this in the past but can think for the life of me how i did this 

so i have 2 datasets where  A=robust name and adresses B= contains a field that would have the name in example - 

a=

Name	Long	Lat
Midgar	34	64

B = 

possible name	Details_1	Detail_2
75 MidGAR street	need this	And and this

i need to join the tables where B.possible_name has A.Name in any format and any space within the field 

i think i used wildcard fuzzy matching but unable to replicate this so far 

any help would be great 

thank you in advance 

[ed_sas_member]

Hi @DaveLarge 

I have tried something like this. Please test it on several cases so that you can see if it sensitive / specific enough.

 

data a;
	infile datalines dlm="09"x;
	input Name:$20. Long Lat;
	datalines;
Midgar	34	64
	;
run;

data b;
	infile datalines dlm="09"x;
	input Possible_Name:$20. Details_1:$20. Details_2:$20.;
	datalines;
75 MidGAR street	need this	And and this
	;
run;

proc sql;
	select a.Name, b.Possible_Name, a.Long, a.Lat, b.Details_1, b.Details_2
	from a full join b /* select the proper type of join */
	on find(compress(b.Possible_Name," "), compress(a.Name," "), "i") > 0;
quit;

Hope this helps!

Best,