topic Re: How to delete duplicate rows using proc sql? in SAS Programming

How to delete duplicate rows using proc sql?

nikunjgattani — Fri, 13 Feb 2015 04:22:30 GMT

Hi,

I wan to delete duplicate rows using proc sql (it is possible by proc sort with nodupkey)

For ex - In the below example, i want to remove duplicates on the basis of name and age.

Input -

id name age company

1 aik 26 tcs

2 aik 29 infosys

3 bik 23 wns

4 bik 23 tcs

5 cik 30 infosys

6 cik 28 wns

Output -

id name age company

1 aik 26 tcs

2 aik 29 infosys

3 bik 23 wns

5 cik 30 infosys

6 cik 28 wns

Re: How to delete duplicate rows using proc sql?

Reeza — Fri, 13 Feb 2015 05:54:02 GMT

Use Select Distinct to get unique records.

proc sql;

create table want as

select distinct id, name, age, company

from have;

quit;

Re: How to delete duplicate rows using proc sql?

nikunjgattani — Fri, 13 Feb 2015 05:58:06 GMT

Hi Reeza,

Your query will create distinct rows, i am looking for distinct name and age combination only.

Thanks

Nikunj

Re: How to delete duplicate rows using proc sql?

Reeza — Fri, 13 Feb 2015 06:00:38 GMT

For duplicates do you care which company gets attached?

You can basically do a group by and summarize on those columns, a data step with first/last gives you more control.

proc sql;

create table want as

select min(id) as ID, name, age, min(company) as company

from have

group by name, age

order by id, name, age;

quit;

Re: How to delete duplicate rows using proc sql?

Ksharp — Fri, 13 Feb 2015 07:58:22 GMT

data have;
input id name $  age company $;
cards;
1 aik 26 tcs
2 aik 29 infosys
3 bik 23 wns
4 bik 23 tcs
5 cik 30 infosys
6 cik 28 wns
;
run;
proc sql;
 select *
  from have
   group by name,age
     having id=min(id);
quit;

Xia Keshan