topic Re: How to clean duplicate records based on minor typos in SAS Programming

How to clean duplicate records based on minor typos

newtosas34 — Tue, 04 Apr 2023 16:47:31 GMT

HI,

I have a dataset that looks like the following. There are duplicate record IDs due to minor typos in the firstname and lastname variables.

Record	FirstName	LastName	Gender
009	Jason	Aman	M
009	Jazon	Aman	M
100	Marshall	Reid	F
101	Reed	Jones	M
102	Aundo	Kalen	F
102	Aundy	Kalen	F
110	Jack	Sarz	M

I would like to fix these duplicates, so there's only one line per record number. I've started fixing them manually, but there are at least 700 observations. This is what I want the dataset to look like:

Record	FirstName	LastName	Gender
009	Jason	Aman	M
100	Marshall	Reid	F
101	Reed	Jones	M
102	Aundy	Kalen	F
110	Jack	Sarz	M

Before continuing with the manual approach, I wanted to check and see if anyone had ideas of a are more programmatic way to fix these duplicate names?

Re: How to clean duplicate records based on minor typos

PaigeMiller — Tue, 04 Apr 2023 17:44:22 GMT

When there are duplicates, which of the duplicate records should be kept?

Re: How to clean duplicate records based on minor typos

newtosas34 — Tue, 04 Apr 2023 18:57:56 GMT

This varies depending on ID. I guess the one that doesn't' have a typo....but is that even possible to specify in SAS?

Re: How to clean duplicate records based on minor typos

PaigeMiller — Tue, 04 Apr 2023 19:00:28 GMT

@newtosas34 wrote:

This varies depending on ID. I guess the one that doesn't' have a typo....but is that even possible to specify in SAS?

There isn't any function in SAS to determine which name is a typo and which isn't. It's probably something you have to do manually (but really, how does that even work, how do you know the person's name isn't really Jazon?)

Re: How to clean duplicate records based on minor typos

SASKiwi — Tue, 04 Apr 2023 20:33:37 GMT

If there is no business rule you can apply to identify a typo (and really there isn't) then you can't code for that. The best you can do is maybe choose arbitrarily one of the names and use that across all instances for that customer.

Re: How to clean duplicate records based on minor typos

PaigeMiller — Tue, 04 Apr 2023 21:18:19 GMT

Let's not forget, that some people really do have unusual spellings in their name. Basketball player Micheal (not Michael) Ray Richardson.

Re: How to clean duplicate records based on minor typos

Ksharp — Wed, 05 Apr 2023 11:20:27 GMT

/*
It is fuzz matched problem.
*/
data have;
infile cards expandtabs;
input (Record	FirstName	LastName	Gender) ($);
cards;
009	Jason	Aman	M
009	Jazon	Aman	M
100	Marshall	Reid	F
101	Reed	Jones	M
102	Aundo	Kalen	F
102	Aundy	Kalen	F
102	Jack	Sarz	M
110	Jack	Sarz	M
110	Kack	Sarz	M
;

proc sql;
create table k as
select a.Record,a.FirstName,b.FirstName as _FirstName,spedis(a.FirstName,b.FirstName) as dis
 from have as a,have as b
  where a.Record=b.Record and a.FirstName ne b.FirstName 
 ;
quit;
data k2;
 set k;
 call sortc(FirstName,_FirstName);
run;
proc sort data=k2 nodupkey;by Record FirstName _FirstName;run;
data k3;
 set k2;
 if dis<50 then group=1;
  else group=dis;
 output;

 if dis<50 then group=1;
  else group=dis+1;
 FirstName=_FirstName;output;
 keep Record FirstName group;
run;
proc sort data=k3 out=k4 ;by Record FirstName group;run;
proc sort data=k4 nodupkey ;by Record FirstName;run;
proc sql;
create table temp as
select a.*,b.group
 from have as a left join k4 as b
  on a.Record=b.Record and a.FirstName=b.FirstName;
quit;
proc sort data=temp out=want nodupkey;
by Record group;
run;