topic Re: Removing duplicates in SAS Procedures

Removing duplicates

jhs2171 — Tue, 01 Nov 2016 15:48:29 GMT

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!

Re: Removing duplicates

LinusH — Tue, 01 Nov 2016 16:06:44 GMT

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.

Re: Removing duplicates

Jim_G — Tue, 01 Nov 2016 16:15:43 GMT

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 ?.

Re: Removing duplicates

jhs2171 — Tue, 01 Nov 2016 16:34:48 GMT

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!

Re: Removing duplicates

Jim_G — Tue, 01 Nov 2016 16:46:53 GMT

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;

Re: Removing duplicates

jhs2171 — Tue, 01 Nov 2016 18:45:49 GMT

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

Re: Removing duplicates

Astounding — Tue, 01 Nov 2016 20:23:54 GMT

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.

Re: Removing duplicates

Jim_G — Wed, 02 Nov 2016 09:53:16 GMT

Change the statement

if first.dob then kid= 1;

to:

if first.border then kid=1;