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
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,
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: