SQL to identify the duplicates

[David_Billa]

I've the table as shown below. If I ignore the 'Serial_Num' field then there are few duplicate records. Now I want to identify the 'Serial_Num' of those duplicate records. Any help to achieve this using SQL?

Serial_Num	Name	Age	Salary	Gender
1	David	34	1000	M
2	David	34	1000	M
3	David	34	1000	M
4	Anu	36	900	F
5	Deepak	24	200	M
6	Deepak	24	200	M
7	James	44	1200	M
8	Anna	37	1000	F

Desired Output;

Serial_Num	Name	Age	Salary	Gender
1	David	34	1000	M
2	David	34	1000	M
3	David	34	1000	M
5	Deepak	24	200	M
6	Deepak	24	200	M

[MayurJadhav]

You can use group by and having clause in proc sql.

@David_Billa 

proc sql;
create table output_table as select * from input_table
group by Name, Age, Salary, Gender having count(*)>=2;
quit;
proc print data=output_table;
run;

[David_Billa]

@MayurJadhav  I've got the error

values "Serial_Num' in select clause is neither an aggregate nor in the group by clause

[Tom]

---

@David_Billa wrote:

@MayurJadhav  I've got the error

values "Serial_Num' in select clause is neither an aggregate nor in the group by clause

---

Are you using PROC SQL?  Or are you running the SQL code in some foreign database?

Show the SAS log for the query that is failing.

[David_Billa]

Yes, I have executed the query in Snowflake.

I believe that we will get the similar error if we execute the similar
query in SAS.

[Kurt_Bremser]

---

@David_Billa wrote:
Yes, I have executed the query in Snowflake.

I believe that we will get the similar error if we execute the similar
query in SAS.

---

No, you won't. You'll get a NOTE about the automatic remerge, which is the purpose of the suggested code.

[David_Billa]

That's good. Any hint how to handle this query in external databases like
Snowflake?

[Kurt_Bremser]

Something like

create table output_table as
  select t1.* 
  from input_table as t1
  inner join (
    select Name, Age, Salary, Gender
    from input_table
    group by Name, Age, Salary, Gender
    having count(*) >= 2
  ) as t2
  on t1.name = t2.name and t1.age = t2.age and t1.salary = t2.salary and t1.gender = t2.gender