I have a dataset with lots of variables. I need to get rid of ALL the records for anyone who is in there more than once based on their Lname, Fname, and Race - the other variables for the same person will not hold the same values, only their Lname, Fname, and Race will be the same. When I say "get rid of", what I really mean is I need all duplicated records to be in their own separate dataset and out of the master set.
Lname Fname Race A B C
Smith John 1 4 2 2
Smith John 1 3 1 6
Adams Leila 2 4 1 5
Walls Marge 4 2 2 2
So Smith, John needs to be annihilated from the master set and put in a separate dataset.
I have tried the following code based on suggestions here in SAS and it is not working. It is not removing any records, period, and has just as many observations as the master set:
PROC SORT DATA = have OUT = Want_NoDups NODUPRECS;
BY Lname Fname Race;
You used NODUPRECS which means all fields need to be identical and beside each other. You want NODUPKEY which means the variables in the BY statement are not duplicated.
I usually recommend a double sort so that you can confidently identify your rule. In this case, you remove duplicates based on last name, first name, race and take the first record, so you keep the lowest value of A for all records with duplicates.
PROC SORT DATA = have ;
BY Lname Fname Race A ;
proc sort data=have out=want nodupkey;
by lname fname race;
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.