02-02-2018 10:13 AM - edited 02-02-2018 10:16 AM
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.
Set have: 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; RUN;
02-02-2018 10:23 AM
Please don't code all in uppercase/mixed.
proc sort data=have out=want_nodups dupout=dropped nodupkey; by lname fname race; run;
Will create want_nodups without duplicates and the rest goes to dropped.
02-02-2018 11:01 AM
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 ; RUN; proc sort data=have out=want nodupkey; by lname fname race; run;