BookmarkSubscribeRSS Feed
lady8506
Quartz | Level 8

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;

 

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

Reeza
Super User

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;

SAS INNOVATE 2024

Innovate_SAS_Blue.png

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. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 497 views
  • 0 likes
  • 3 in conversation