BookmarkSubscribeRSS Feed
gspila
Calcite | Level 5

Hello everyone, I have a database with lot of rubbish, soon we will change our database and I'd like to clean as much as possible the data before the migration.
I'm trying to identify possible duplicated records inside one dataset, which are not exact match but are similar.
For example :
Record 1 - Name=John, Surname=Doe, Address= Fake Street
Record 2 - Name=Jonh, Surname= Doe Joe, Address=F. Street

My idea is to create a unique string with name, surname, address, without spaces, (for example johndoefakestreet) and confront one to one all the record with all other record in the same dataset, (approximately 800k records) using compged function, and keep only the record with the smallest value in order to identify possible duplicates (which I know there are present).

I don't know how to perform this operation, or if there is an easiest way to do this.

 

I'm using sas 9.4, i hope it's clear what I'm trying to do

Thanks!

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Do a proc freq on your data, this wy you will get a list of distinct values and how many times that value appears.  You can then use that output to see where cleaning can be done.  Iterate that process and as the list decreases it should go quicker.

ballardw
Super User

Combining everything into one field may introduce more problems than you think.

 

When I have a project like this I use a free tool developed by the CDC call LinkPlus which is a probabalistic matching program available at https://www.cdc.gov/cancer/npcr/tools/registryplus/lp_tech_info.htm

 

The program returns a probability of a match and indicators of which records it may match.

Reeza
Super User

I'll second @ballardw suggestion. 

Otherwise for fuzzy matches look at:

 

SPEDIS, COMPGED type functions as well as this post that has a good SQL example of doing this type of multiple match in a semi-brute force method.

https://communities.sas.com/t5/SAS-Procedures/Name-matching/td-p/82780

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 804 views
  • 3 likes
  • 4 in conversation