How to make a match between two variables similar to the merge but that the condition is that they are not exactly the same, but just similar?
For example I have 2 datasets:
dataset1-var_street
Manhattan street 1000
dataset2-var_street
Madison 1000
Manhattan st 1000
Manhattan street 1000
Manhatan street 1000
Manhattan st. 1000
Moore 1000
Manhattan 1100
Manhattan street 100
Manhattan st. 2000
If I use MERGE function the result is only one obs:
dataset3-var_street
Manhattan street 1000
But I want some like this:
dataset3-var_street
Manhattan st 1000
Manhattan street 1000
Manhatan street 1000
Manhattan st. 1000
Your trying to do a fuzzy match. You can find a number of possibilities searching for that term.
Here is one method. You are looking for low scores, but have to decide how low is still a good match. In your example, 300 or lower would work:
data dataset1; informat var_street $100.; input var_street & number; cards; Manhattan street 1000 ; data dataset2; informat var_street $100.; input var_street & number; cards; Madison 1000 Manhattan st 1000 Manhattan street 1000 Manhatan street 1000 Manhattan st. 1000 Moore 1000 Manhattan 1100 Manhattan street 100 Manhattan st. 2000 ; /* get number of records in bankinfo dataset */ data _null_; if 0 then set dataset2 nobs=nobs; call symput('numrec',nobs); stop; run; data test (keep=var_street number compare_add compare_num score); array addresses(&numrec) $100; array numbers(&numrec); do i=1 to &numrec; set dataset2(rename=(var_street=add_check number=num_check)); addresses(i)=add_check; numbers(i)=num_check; end; do until (eof); set dataset1 end=eof; do i=1 to &numrec; if number eq numbers(i) then do; compare_add=addresses(i); compare_num=numbers(i); score= compged(var_street,addresses(i)); output; end; end; end; run;
Art, CEO, AnalystFinder.com
Your trying to do a fuzzy match. You can find a number of possibilities searching for that term.
Here is one method. You are looking for low scores, but have to decide how low is still a good match. In your example, 300 or lower would work:
data dataset1; informat var_street $100.; input var_street & number; cards; Manhattan street 1000 ; data dataset2; informat var_street $100.; input var_street & number; cards; Madison 1000 Manhattan st 1000 Manhattan street 1000 Manhatan street 1000 Manhattan st. 1000 Moore 1000 Manhattan 1100 Manhattan street 100 Manhattan st. 2000 ; /* get number of records in bankinfo dataset */ data _null_; if 0 then set dataset2 nobs=nobs; call symput('numrec',nobs); stop; run; data test (keep=var_street number compare_add compare_num score); array addresses(&numrec) $100; array numbers(&numrec); do i=1 to &numrec; set dataset2(rename=(var_street=add_check number=num_check)); addresses(i)=add_check; numbers(i)=num_check; end; do until (eof); set dataset1 end=eof; do i=1 to &numrec; if number eq numbers(i) then do; compare_add=addresses(i); compare_num=numbers(i); score= compged(var_street,addresses(i)); output; end; end; end; run;
Art, CEO, AnalystFinder.com
Art, I have one question, what happend if I have more than 1 obs in dataset 1:
For example:
data dataset1;
informat var_street $100.;
input var_street & number;
cards;
Manhattan street 1000
Moore 1001
;
And I want to compare two addresses Manhattan street 1000 and Moore 1001 ?
@Angel_Saenz: The code, as is, will accomodate any number of records in either of the two datasets. Until you discover what value provides false positives, you could always sort the resulting file by
var_street number score
and then, possibly, only keep the record (for each var_street number) with the lowest score.
Art, CEO, AnalystFinder.com
If you've got the SAS Data Quality Server (comes also with offerings like the SAS Data Management bundle I believe) then you could first standardize your addresses and then use these standardized addresses for the merge.
With the DQ server ("DataFlux") you could also create match codes and then merge over these match codes.
With match codes you'll end up with a few wrong matches, which standardized addresses you'll miss a few matches.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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: