## delete duplicate records

Solved
Occasional Contributor
Posts: 14

# delete duplicate records

HI..

I have a sitation where I need to delete all duplicated records without keep one unique row.Can anyone help me to achieve this .

For example...

id name dept

1 aaa    xxx

1 aaa    xxx

2 bbb    yyy

3 ccc    zzz

3 ccc    zzz

4 ddd    zzz

result should be

id name dept

2  bbb    yyy

4  ddd    zzz

Accepted Solutions
Solution
‎09-23-2011 04:21 AM
Super User
Posts: 10,784

## delete duplicate records

```data temp;
input id name \$ dept \$;
cards;
1 aaa    xxx
1 aaa    xxx
2 bbb    yyy
3 ccc    zzz
3 ccc    zzz
4 ddd    zzz
;
run;
proc sql noprint;
create table want as
select *
from temp
group by id,name,dept
having count(*) eq 1;
quit;

data temp;
input id name \$ dept \$;
cards;
1 aaa    xxx
1 aaa    xxx
2 bbb    yyy
3 ccc    zzz
3 ccc    zzz
4 ddd    zzz
;
run;
proc sort data=temp;by id name dept;run;
data want;
set temp;
by id name dept;
if first.dept and last.dept;
run;

```

Ksharp

All Replies
Solution
‎09-23-2011 04:21 AM
Super User
Posts: 10,784

## delete duplicate records

```data temp;
input id name \$ dept \$;
cards;
1 aaa    xxx
1 aaa    xxx
2 bbb    yyy
3 ccc    zzz
3 ccc    zzz
4 ddd    zzz
;
run;
proc sql noprint;
create table want as
select *
from temp
group by id,name,dept
having count(*) eq 1;
quit;

data temp;
input id name \$ dept \$;
cards;
1 aaa    xxx
1 aaa    xxx
2 bbb    yyy
3 ccc    zzz
3 ccc    zzz
4 ddd    zzz
;
run;
proc sort data=temp;by id name dept;run;
data want;
set temp;
by id name dept;
if first.dept and last.dept;
run;

```

Ksharp

🔒 This topic is solved and locked.