topic Re: Address Matching in SAS Programming

Address Matching

syazwaan — Thu, 11 Mar 2021 09:42:48 GMT

I have 2 datasets (Data and Locality). I want to match string between column 'ADDR1' in first dataset (Data) with 'Locality Name' in second dataset (Locality). The 'Data' dataset contain 800,000+ rows and the 'Locality' dataset contain 2,000+ rows.

Example:

ADDR1	Locality Name
NO 35 JALAN BUKIT BERUANG TIGA TAMAN BUKIT BERUANG	KAMPUNG TERSUSUN SERKAM DARAT
LOT 2697 JALAN TIONG KAMPUNG TERSUSUN SERKAM DARAT	TAMAN REMBIA SETIA
RM 1024 JALAN REMBIA SETIA 5 TAMAN REMBIA SETIA	JALAN BUKIT BERUANG
RM 713 JALAN SETIA 3 TAMAN REMBIA SETIA	JALAN BUKIT BERUANG

Result that I want:

ADDR1	Locality Name
NO 35 JALAN BUKIT BERUANG TIGA TAMAN BUKIT BERUANG	JALAN BUKIT BERUANG
LOT 2697 JALAN TIONG KAMPUNG TERSUSUN SERKAM DARAT	KAMPUNG TERSUSUN SERKAM DARAT
RM 1024 JALAN REMBIA SETIA 5 TAMAN REMBIA SETIA	TAMAN REMBIA SETIA
RM 713 JALAN SETIA 3 TAMAN REMBIA SETIA	TAMAN REMBIA SETIA

Thank you.

Re: Address Matching

andreas_lds — Thu, 11 Mar 2021 08:58:12 GMT

Please post both datasets in usable form.

The data you have posted looks like you want an exact match, so sorting the data and using data step with merge should solve the issue.

Re: Address Matching

syazwaan — Thu, 11 Mar 2021 09:43:33 GMT

Example:

ADDR1	Locality Name
NO 35 JALAN BUKIT BERUANG TIGA TAMAN BUKIT BERUANG	KAMPUNG TERSUSUN SERKAM DARAT
LOT 2697 JALAN TIONG KAMPUNG TERSUSUN SERKAM DARAT	TAMAN REMBIA SETIA
RM 1024 JALAN REMBIA SETIA 5 TAMAN REMBIA SETIA	JALAN BUKIT BERUANG
RM 713 JALAN SETIA 3 TAMAN REMBIA SETIA	JALAN BUKIT BERUANG

Result:

ADDR1	Locality Name
NO 35 JALAN BUKIT BERUANG TIGA TAMAN BUKIT BERUANG	JALAN BUKIT BERUANG
LOT 2697 JALAN TIONG KAMPUNG TERSUSUN SERKAM DARAT	KAMPUNG TERSUSUN SERKAM DARAT
RM 1024 JALAN REMBIA SETIA 5 TAMAN REMBIA SETIA	TAMAN REMBIA SETIA
RM 713 JALAN SETIA 3 TAMAN REMBIA SETIA	TAMAN REMBIA SETIA

Re: Address Matching

Kurt_Bremser — Thu, 11 Mar 2021 09:23:50 GMT

Simple SQL join:

proc sql;
create table want as
  select
    a.ADDR1,
    b.Locality_Name
  from have1 a left join have2 b
  on a.ADDR1 = b.Locality_Name
;
quit;

String Matching

syazwaan — Fri, 12 Mar 2021 01:53:53 GMT

Example:

ADDR1	Locality Name
NO 35 JALAN BUKIT BERUANG TIGA TAMAN BUKIT BERUANG	KAMPUNG TERSUSUN SERKAM DARAT
LOT 2697 JALAN TIONG KAMPUNG TERSUSUN SERKAM DARAT	TAMAN REMBIA SETIA
RM 1024 JALAN REMBIA SETIA 5 TAMAN REMBIA SETIA	JALAN BUKIT BERUANG
RM 713 JALAN SETIA 3 TAMAN REMBIA SETIA	JALAN BUKIT BERUANG

Result that I want:

ADDR1	Locality Name
NO 35 JALAN BUKIT BERUANG TIGA TAMAN BUKIT BERUANG	JALAN BUKIT BERUANG
LOT 2697 JALAN TIONG KAMPUNG TERSUSUN SERKAM DARAT	KAMPUNG TERSUSUN SERKAM DARAT
RM 1024 JALAN REMBIA SETIA 5 TAMAN REMBIA SETIA	TAMAN REMBIA SETIA
RM 713 JALAN SETIA 3 TAMAN REMBIA SETIA	TAMAN REMBIA SETIA

Thank you.

Re: String Matching

PGStats — Fri, 12 Mar 2021 03:32:11 GMT

You could get the simplest matches with the CONTAINS operator:

proc sql;
create table result as
select 
	a.addr1,
	b.localityName
from
	data as a left join
	locality as b on a.addr1 contains strip(b.localityName);
quit;

Re: String Matching

andreas_lds — Fri, 12 Mar 2021 06:53:51 GMT

@syazwaan: i merged your new message titled "String Matching" with the older message. Please don't double post questions.