topic Re: Flagging Unique and Duplicates in SAS Programming

Flagging Unique and Duplicates

Eugenio211 — Wed, 06 Jan 2021 20:07:38 GMT

Hi - I just want to ask some help with regards to flagging Unique and Duplicates.

below is my sample data, in excel I used '=IF(I2=I1,1,0)' how do we translate that to sas? thank you.

CUSTOMER_KEY	DidIntLoan	Uniq
Customer A	0	0
Customer B	0	0
Customer B	0	1
Customer B	0	1
Customer C	0	0
Customer C	0	1
Customer D	0	0

Re: Flagging Unique and Duplicates

Kurt_Bremser — Wed, 06 Jan 2021 20:17:01 GMT

The LAG function retrieves values from a previous observation.

Re: Flagging Unique and Duplicates

Jagadishkatam — Wed, 06 Jan 2021 20:22:43 GMT

please try below code

data have;
input CUSTOMER_KEY$10.	DidIntLoan	;
cards;
CustomerA	0
CustomerB	0
CustomerB	0
CustomerB	0
CustomerC	0
CustomerC	0
CustomerD	0
;

data want;
set have;
by CUSTOMER_KEY;
retain cnt;
if first.CUSTOMER_KEY then cnt=1;
else cnt+1;
if cnt=1 then uniq=0;
else uniq=1;
drop cnt;
run;

Re: Flagging Unique and Duplicates

novinosrin — Wed, 06 Jan 2021 20:36:44 GMT

data have;
input CUSTOMER_KEY$10.	DidIntLoan	;
cards;
CustomerA	0
CustomerB	0
CustomerB	0
CustomerB	0
CustomerC	0
CustomerC	0
CustomerD	0
;

data want;
 set have;
 by CUSTOMER_KEY;
 if first.CUSTOMER_KEY and last.customer_key then uniq=0;
 else uniq=ifn(first.CUSTOMER_KEY,0,1);
run;

CUSTOMER_KEY	DidIntLoan	uniq
CustomerA	0	0
CustomerB	0	0
CustomerB	0	1
CustomerB	0	1
CustomerC	0	0
CustomerC	0	1
CustomerD	0	0

Re: Flagging Unique and Duplicates

novinosrin — Wed, 06 Jan 2021 20:38:33 GMT

or just-


data want;
 set have;
 by CUSTOMER_KEY;
 if first.CUSTOMER_KEY and last.customer_key then uniq=0;
 else uniq=not first.CUSTOMER_KEY;
run;

Re: Flagging Unique and Duplicates

Eugenio211 — Wed, 06 Jan 2021 20:53:24 GMT

Thank you for the quick response.

Re: Flagging Unique and Duplicates

novinosrin — Wed, 06 Jan 2021 21:12:54 GMT

Or even shorter 🙂 haha test carefully though plz-

data want;
 set have;
 by CUSTOMER_KEY;
 uniq=not first.CUSTOMER_KEY;
run;