Help using Base SAS procedures

Removing duplicates

Reply
Contributor
Posts: 27

Removing duplicates

Hello, 

 

I am trying to find duplicates and only keep the unique obs (doesn't matter if it is the first or the last record). I used the variable called Plural to identify them and Plural has three categories (1, 2, and 3). Plural=1 means there are no duplicates, Plural=2 means there are two records (per person) and Plural=3 means there are three records (per person). I tried: 

 

data nodup; 

set dup 

where plural >1; 

by time; 

if first.time;run; 

 

and obviously, the dataset nodup only has the one w/ Plural>1. Is there any way I can keep every record in the dataset (plural=1, 2, or 3), but remove duplicates if Plural>1? 

 

Thank you!

Super User
Posts: 5,424

Re: Removing duplicates

I don't think that you could use Plural solely.
If you still have the key to identify what you think is unique, skip plural and use PROC SORT NODUPKEY instead.
Data never sleeps
Frequent Contributor
Posts: 95

Re: Removing duplicates

Please be more specific about what you want to remove.    When you say remove duplicates that implies remove duplicate records and yet you say you want to keep all records. what do you want to remove if not the record ?.  

Contributor
Posts: 27

Re: Removing duplicates

Hi, 

 

I want to remove duplicate records (e.g. where plural >1), but I want all unique records (including where plural=1) in the dataset not just the ones with multiple records (plural>1). I hope this makes sense. 

 

Thanks!

Frequent Contributor
Posts: 95

Re: Removing duplicates

I think that you want to keep the first of the duplicate records but delete the second and third dup.

 

Try this:

 

 

data;    set;   by time;

 

if first.time;

Contributor
Posts: 27

Re: Removing duplicates

Hi Jim_G: 

 

Thank you for your comment- Okay so I tweaked what you suggested and tried the code below: Basically, I sorted by enough keys to get a unique combination per row; then use a data step to number each row as I want. 

 

proc sort data=old; by id dob border; run;

 

data new;
set old;
by id dob border;

retain kid;
if first.dob then    kid= 1;
else                     kid= 1+kid; run; 

 

Not sure what I am missing here, but all the observations in my dataset have kid=1. In theory, they should be numbered 1-2-3 and then next record to a different person would be numbered 1. Any suggestion?

 

Thank you 

Super User
Posts: 5,497

Re: Removing duplicates

It's looking like this statement needs to change:

 

if first.dob then kid=1;

 

Probably, it should become (with no other changes required):

 

if first.id then kid=1;

 

Basically, you are numbering the records for each ID/DOB combination.  Your results indicate there is only one observation for each group.  The change will number the records for each ID.  Hopefully, that's what you are hoping to do.

Frequent Contributor
Posts: 95

Re: Removing duplicates

[ Edited ]
Posted in reply to Astounding

Change the statement

 

                if first.dob then    kid= 1;

 

to:          

                if first.border then kid=1;

 

 

 

 

Ask a Question
Discussion stats
  • 7 replies
  • 324 views
  • 1 like
  • 4 in conversation