I have a dataset that looks like the following. There are duplicate record IDs due to minor typos in the firstname and lastname variables.
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:
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?
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?)
/* 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;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.