BookmarkSubscribeRSS Feed
DaveLarge
Calcite | Level 5

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 LongLat
Midgar3464

 

B = 

 

possible name Details_1Detail_2
75 MidGAR streetneed thisAnd 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 

1 REPLY 1
ed_sas_member
Meteorite | Level 14

Hi @DaveLarge 

 

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

Capture d’écran 2020-05-05 à 16.45.05.png

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,

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 247 views
  • 0 likes
  • 2 in conversation