BookmarkSubscribeRSS Feed
newtosas34
Calcite | Level 5

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. 

RecordFirstNameLastNameGender
009JasonAmanM
009JazonAmanM
100MarshallReidF
101ReedJonesM
102AundoKalenF
102AundyKalenF
110JackSarzM

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:

RecordFirstNameLastNameGender
009JasonAmanM
100MarshallReidF
101ReedJonesM
102AundyKalenF
110JackSarzM

 

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?

6 REPLIES 6
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
newtosas34
Calcite | Level 5

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

PaigeMiller
Diamond | Level 26

@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?)

--
Paige Miller
SASKiwi
PROC Star

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. 

PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Ksharp
Super User
/*
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;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 6 replies
  • 811 views
  • 1 like
  • 4 in conversation