Help using Base SAS procedures

Getting rid of duplicates based on the values of multiple variables

Reply
Contributor
Posts: 37

Getting rid of duplicates based on the values of multiple variables

[ Edited ]

Hello everyone,

 

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;

 

Super User
Super User
Posts: 9,599

Re: Getting rid of duplicates based on the values of multiple variables

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.

 

Super User
Posts: 23,754

Re: Getting rid of duplicates based on the values of multiple variables

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;
Ask a Question
Discussion stats
  • 2 replies
  • 87 views
  • 0 likes
  • 3 in conversation