topic Re: Removing Duplicates in SAS Procedures

Removing Duplicates

_maldini_ — Tue, 10 Jan 2017 20:46:30 GMT

I am analyzing survey data. I want to remove respondents that completed the survey more than once. A five-digit code was generated that enabled repeat responders to be identified (Variable name = privatecode).

I see various ways of using PROC SORT to remove duplicates and leave one observation (i.e. NODUPRECS, NODUPKEY). I want to remove ALL observations with the same private code.

How can I accomplish this?

Thanks!

Re: Removing Duplicates

Shmuel — Tue, 10 Jan 2017 20:56:34 GMT

Do you mean that you want to filter any privatecode if occures more than once ?

If so do:

proc sort data=have; by privatecode; run;

data want;

set have;

by privatecode;

if not (first.privatecode and last.privatecode) then delete;

run;

Re: Removing Duplicates

_maldini_ — Tue, 10 Jan 2017 20:58:05 GMT

If by "filter" you mean remove, then yes!

Re: Removing Duplicates

ballardw — Tue, 10 Jan 2017 21:00:36 GMT

Identify the singles and keep. Proc sql will do this:

proc sql;
   create table want as
   select b.*
   from (
         select distinct privatecode
         from YOURDATESET
         group by privatecode
         having count(*)=1
        ) as a 
     left join YOURDATASET as b
     on a.private=b.privatecode;
quit;

Re: Removing Duplicates

_maldini_ — Tue, 10 Jan 2017 23:57:05 GMT

@Shmuel

There is one problem. Almost 400 observations have privatecode missing. These observations are being deleted because the privatecode is the same, missing.

What's the best work-around here?

Thanks!

Re: Removing Duplicates

ballardw — Wed, 11 Jan 2017 00:11:56 GMT

if not (first.privatecode and last.privatecode) and not missing(privatecode) then delete;

Re: Removing Duplicates

Shmuel — Wed, 11 Jan 2017 05:58:47 GMT

@ballardw gave you the right answer how do include all observations with miising privatecode.

@ballardw - is it possible to be done with the SQL too ?

Re: Removing Duplicates

ballardw — Wed, 11 Jan 2017 15:32:22 GMT

@Shmuel wrote:

@ballardw - is it possible to be done with the SQL too ?

I'm not going to recreate a dataset to test since the OP didn't provide example data (hint)

I would start by changing

having count(*)=1

having count(*)=1 or missing(a.privatecode)

this is untested.

Re: Removing Duplicates

Shmuel — Wed, 11 Jan 2017 18:07:57 GMT

In order to include all observations with missing privatecode, change my code to:

data want;
 set have;
   by privatecode;
       
       if not (first.privatecode and last.privatecode) 
       and not missing(privatecode) then delete;
run;