topic Flagging up duplicates in SAS Programming

Flagging up duplicates

zetter — Thu, 04 Sep 2014 18:20:42 GMT

Hi I’ve got a table like this

ID fruit colour

555 avocado orange

111 orange orange

111 cabbage green

123 mango green

333 strawberry red

555 berry orange

I want sas to look at all of the IDS above and flag if similar ID exist, like this:

ID fruit colour duplicate?

555 avocado Orange No

111 orange Orange No

111 cabbage green Yes

123 mango green No

333 strawberry red No

333 strawberry red Yes

555 berry orange Yes

Thanks.

Re: Flagging up duplicates

stat_sas — Thu, 04 Sep 2014 18:34:43 GMT

proc sort data=have;
by id;
run;

data want;
set have;
length duplicate $5.;
by id;
duplicate='No';
if last.id then duplicate='Yes';
if first.id and last.id then duplicate='No';
run;

Re: Flagging up duplicates

Haikuo — Thu, 04 Sep 2014 18:38:33 GMT

if you want to maintain the original data order:

data have;

input ID (fruit colour ) (:$10.);

cards;

555 avocado orange

111 orange orange

111 cabbage green

123 mango green

333 strawberry red

555 berry orange

;

data want;

if _n_=1 then

do;

dcl hash h();

h.definekey('id');

h.definedone();

end;

set have;

length dup $3;

if h.check()=0 then

dup='Yes';

else

do;

rc=h.add();

dup='No';

end;

drop rc;

run;

Haikuo

Re: Flagging up duplicates

Hima — Thu, 04 Sep 2014 18:39:44 GMT

DATA HAVE;
INPUT ID       fruit    $                 colour $;
DATALINES;
555     avocado               orange
111    orange                 orange
111    cabbage                green
123     mango                 green
333    strawberry             red
333    strawberry             red
555      berry                orange
;
RUN;

PROC SORT DATA=HAVE;
BY ID;
RUN;

DATA WANT;
SET HAVE;
LENGTH DUPLICATE $3.;
BY ID;
IF not(FIRST.ID) THEN
DUPLICATE='yes';
ELSE DUPLICATE='NO';
RUN;

Re: Flagging up duplicates

Tom — Thu, 04 Sep 2014 23:58:42 GMT

You as basically asking for NOT FIRST.ID.

data have ;

input id fruit :$10. color :$10. ;

cards;

555 avocado orange

111 orange orange

111 cabbage green

123 mango green

333 strawberry red

555 berry orange

run;

proc sort; by id; run;

data want ;

set have ;

by id ;

if first.id then dup='NO ';

else dup='YES';

put (_all_) (:);

run;

111 orange orange NO

111 cabbage green YES

123 mango green NO

333 strawberry red NO

333 strawberry red YES

555 avocado orange NO

555 berry orange YES

Re: Flagging up duplicates

amats — Fri, 05 Sep 2014 00:40:27 GMT

Another way.

proc sort data=HAVE out=WANT1 dupout=WANT2 nodupkey;

by ID;

run;

data WANT;

set WANT1 WANT2 (in=_IN);

by ID;

if _IN then DUP = "Yes";

else DUP = "No";

run;