Often you may want to join together two datasets in SAS based on imperfectly matching strings.
This is often called fuzzy matching.
The easiest way to perform fuzzy matching in SAS is to use the SOUNDEX function along with the COMPGED function.
Both of these functions are used to quantify the similarity between strings and can be used to “match” similar strings together.
The following example shows how to use these functions to perform fuzzy matching in SAS.
Example: How to Perform Fuzzy Matching in SAS
Suppose we have the following dataset in SAS that contains information about team names and points for various basketball players:
/*create first dataset*/
data data1;
input team $ points;
datalines;
Mavs 19
Nets 22
Kings 34
Warriors 19
Magic 32
;
run;
/*view dataset*/
proc print data=data1;
And suppose we have another dataset with team names and assists for various basketball players:
/*create second dataset*/
data data2;
input team $ assists;
datalines;
Netts 8
Majick 7
Keengs 8
Warriors 12
Mavs 4
;
run;
/*view dataset*/
proc print data=data2;
Notice that many of the team names in this dataset are similar but not exactly the same as the team names in the previous dataset.
We can use the following syntax in SAS to perform fuzzy matching and join together these two datasets based on similar team names:
/*use fuzzy matching to merge datasets based on similar team names*/ data data3; set data1; tmp1=soundex(team); /*encode team names from data1*/ do i=1 to nobs; set data2(rename=(team=team2)) point=i nobs=nobs; tmp2=soundex(team2); /*encode team names from data2*/ dif=compged(tmp1,tmp2); /*determine similarity between team names*/ if dif<=50 then do; drop i tmp1 tmp2 dif; /*drop unnecessary variables*/ output; end; end; run; /*view resulting dataset*/ proc print data=data3;
The SOUNDEX and COMPGED functions are able to match team names based on similarity and produce one final dataset that merges the two datasets together.
Additional Resources
The following tutorials explain how to perform other common tasks in SAS:
SAS: How to Perform One-to-Many Merge
SAS: How to Use (in=a) in Merge Statement
SAS: How to Merge If A Not B
This is great,
Although I would caution with using the soundex function. I am guessing it helps processing times, but can result in completely different words being a “perfect” match, as the soundex algorithm is not lossless.
For example, I am matching company names and
“KLAVIYO INC”
“Kula Bio Inc”
“Kalpa Inc”
“KLv1 Inc”
All encode into the exact same output: “K4152”
Which means the code is saying they are a perfect match when they really should not be.
Hi Tyler…You’re absolutely right to caution against relying solely on the Soundex function for matching tasks, especially when dealing with critical data like company names. The Soundex algorithm is a phonetic algorithm designed to group similar-sounding words, but as you pointed out, it can sometimes lead to false positives by encoding different words or names into the same value.
### Alternatives to Soundex:
For tasks like matching company names, where accuracy is crucial, you might consider the following alternatives or enhancements:
1. **Levenshtein Distance (Edit Distance):**
– This algorithm measures the difference between two strings by counting the minimum number of single-character edits (insertions, deletions, or substitutions) required to change one string into the other. It provides a more granular comparison than Soundex.
– **Use Case:** “KLAVIYO INC” and “Kula Bio Inc” would have a higher Levenshtein distance, indicating they are not a close match.
2. **Jaro-Winkler Distance:**
– This algorithm is a variation of the Levenshtein distance and is particularly effective for short strings, giving higher scores to strings that match from the beginning.
– **Use Case:** It might be more effective in distinguishing between company names like “KLAVIYO INC” and “Kalpa Inc.”
3. **TF-IDF with Cosine Similarity:**
– Transform the names into TF-IDF vectors and then calculate cosine similarity. This approach considers the importance of each term in the context of the entire dataset, providing a more nuanced similarity score.
– **Use Case:** It works well when matching names in large datasets, taking into account the frequency of terms.
4. **Double Metaphone:**
– An improvement on Soundex, Double Metaphone generates two phonetic codes for each word, which can help distinguish between words that Soundex might incorrectly group together.
– **Use Case:** It might offer better precision when comparing names like “KLAVIYO INC” and “Kalpa Inc.”
5. **Hybrid Approach:**
– Combining multiple methods can provide more accurate results. For instance, you could first use a phonetic algorithm like Double Metaphone to group names and then apply Levenshtein or Jaro-Winkler to refine the matches within each group.
– **Use Case:** This would reduce the chances of false positives while still maintaining processing efficiency.
### Example Implementation of Levenshtein Distance in Python:
“`python
from Levenshtein import distance as levenshtein_distance
# Example names
name1 = “KLAVIYO INC”
name2 = “Kula Bio Inc”
name3 = “Kalpa Inc”
name4 = “KLv1 Inc”
# Calculate Levenshtein Distance
dist1 = levenshtein_distance(name1, name2)
dist2 = levenshtein_distance(name1, name3)
dist3 = levenshtein_distance(name1, name4)
print(f”Levenshtein Distance between ‘{name1}’ and ‘{name2}’: {dist1}”)
print(f”Levenshtein Distance between ‘{name1}’ and ‘{name3}’: {dist2}”)
print(f”Levenshtein Distance between ‘{name1}’ and ‘{name4}’: {dist3}”)
“`
### Summary:
– **Accuracy vs. Speed:** While Soundex is fast and efficient, it sacrifices accuracy in cases where small differences matter. For more accurate matching, consider using algorithms like Levenshtein distance or Jaro-Winkler, possibly in combination with a phonetic algorithm.
– **Tailored Approach:** Depending on the nature of your data and the importance of accuracy, you might need a hybrid approach to balance performance and precision.