topic Re: As MERGE function but not exactly, just similar in SAS Programming

As MERGE function but not exactly, just similar

Angel_Saenz — Sun, 24 Sep 2017 14:03:58 GMT

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

Re: As MERGE function but not exactly, just similar

art297 — Sun, 24 Sep 2017 15:19:41 GMT

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

Re: As MERGE function but not exactly, just similar

Patrick — Sun, 24 Sep 2017 21:29:30 GMT

@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.

Re: As MERGE function but not exactly, just similar

Angel_Saenz — Tue, 26 Sep 2017 16:13:50 GMT

GREAT! thank you Art!

Re: As MERGE function but not exactly, just similar

Angel_Saenz — Tue, 26 Sep 2017 20:13:00 GMT

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 ?

Re: As MERGE function but not exactly, just similar

art297 — Tue, 26 Sep 2017 20:52:58 GMT

@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