topic Re: Fuzzy match postal codes in SAS Programming

Fuzzy match postal codes

chrishull — Wed, 16 Mar 2022 18:34:59 GMT

Hi All,

I'm looking for help matching postal codes. I have a list of postal codes in one table and a list of postal codes and latitude and longitude in another. I need to match the postal codes by either all 6 or the first instance where 5 (or 4 or 3) characters match.

example:

list of codes L1G3H8 L1G3J0 L1G3J1 L1G3J2 L1G3J3

list of codes with lat and long L1G3J1 43.920899 -78.874794 L1G3J2 43.920732 -78.874717 L1G3J3 43.920354 -78.876291

desired result

L1G3H8 43.920899 -78.874794 L1G3J0 43.920899 -78.874794 L1G3J1 43.920899 -78.874794 L1G3J2 43.920732 -78.874717 L1G3J3 43.920354 -78.876291

Re: Fuzzy match postal codes

mkeintz — Wed, 16 Mar 2022 22:07:47 GMT

What do you mean by

the first instance where 5 (or 4 or 3) characters match.

Why "first" match? Shouldn't all matching 5 character codes have an equal chance of being randomly selected?

Re: Fuzzy match postal codes

SASKiwi — Wed, 16 Mar 2022 20:27:54 GMT

Start with an exact match on all 6 characters, then with the remaining rows a 5 character match. The problem is that with any matches with less than 6 characters you are not necessarily going to get a unique match. What post code will you choose when there are multiple choices? The first or last sorted value or some other strategy?

Re: Fuzzy match postal codes

Reeza — Wed, 16 Mar 2022 21:36:10 GMT

Create copies of your Postal codes with 3/4 chars each and for those take the average of the latitude/longitude.

So L1G3H8 becomes L1G3H* and the latitude and longitude are the average of all the 9 postal codes in that list.
Repeat for 4 characters.
For 3 characters, that is a Forward Sortation Area (FSA, assuming Canada) and use the FSA centroid list or do the same as above as well.

data codes345;
set fullCodes;
output; * 6 characters;
PC = substr(PC, 1, 5);
output; *5 characters;
PC = substr(PC, 1, 4);
output; *4 characters;
PC = substr(PC, 1, 3);
output; *3 characters;
run;

proc means data=codes345 noprint mean NWAY;
class PC;
var latitude longitude;
ods output summary=PC_Lookup;
run;

Now use the PC_LOOKUP table to merge with your data.