topic Re: finding duplicate records in SAS Programming

finding duplicate records

vThanu — Wed, 21 Nov 2018 08:10:58 GMT

Data ds;

input first $ last $ age;

datalines;

sam sunder 20

sunder sam 20

raj gopal 22

rama rao 21

gopal raj 22

;

run;

In the above code 1st observation and 2nd observation are duplicate (first & last are interchanged) & same for 3rd & 5th obs.

I want to eliminate this type of duplicate records.

please can any one help me out (on SAS BASE SAS programm).

Re: finding duplicate records

vThanu — Wed, 21 Nov 2018 08:56:22 GMT

plz can any one help me out

Re: finding duplicate records

RW9 — Wed, 21 Nov 2018 09:14:28 GMT

You could do:

Data ds;
  input first $ last $ age;
datalines;
sam sunder 20
sunder sam 20
raj gopal 22
rama rao 21
gopal raj 22
;
run;

data want;
  set ds;
  call sortc(first,last);
run;

proc sort data=want nodupkey;
  by first last;
run;

Re: finding duplicate records

kiranv_ — Wed, 21 Nov 2018 09:56:49 GMT

@RW9 code is nice and clean. another way to do using SQL and this will make sure, which record to pick in this case earlier records are picked.

Data ds;
  input first $ last $ age;
datalines;
sam sunder 20
sunder sam 20
raj gopal 22
rama rao 21
gopal raj 22
;
run;

data ds1;
set ds;
var = _n_;
run;


proc sql;
create table want(drop=var) as 
select * from ds1
except
select a.* from ds1 a
inner join 
ds1 b
on a.first = b.last
and a.last = b.first
and a.age = b.age
and a.var gt b.var;

Re: finding duplicate records

vThanu — Wed, 21 Nov 2018 10:45:25 GMT

great... thank you ...