Solved
Contributor
Posts: 47

# As MERGE function but not exactly, just similar

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

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

Accepted Solutions
Solution
‎09-26-2017 12:13 PM
PROC Star
Posts: 8,167

## Re: As MERGE function but not exactly, just similar

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;
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 numbers(&numrec);
do i=1 to &numrec;
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_num=numbers(i);
output;
end;
end;
end;
run;
```

Art, CEO, AnalystFinder.com

All Replies
Solution
‎09-26-2017 12:13 PM
PROC Star
Posts: 8,167

## Re: As MERGE function but not exactly, just similar

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;
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 numbers(&numrec);
do i=1 to &numrec;
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_num=numbers(i);
output;
end;
end;
end;
run;
```

Art, CEO, AnalystFinder.com

Contributor
Posts: 47

## Re: As MERGE function but not exactly, just similar

GREAT! thank you Art!
Contributor
Posts: 47

## Re: As MERGE function but not exactly, just similar

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 ?

PROC Star
Posts: 8,167

## Re: As MERGE function but not exactly, just similar

@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

Posts: 4,737

## Re: As MERGE function but not exactly, just similar

[ Edited ]

@Angel_Saenz

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.

☑ This topic is solved.