topic Re: SQL to identify the duplicates in SAS Programming

SQL to identify the duplicates

David_Billa — Tue, 18 Apr 2023 10:40:43 GMT

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

Re: SQL to identify the duplicates

MayurJadhav — Tue, 18 Apr 2023 10:57:56 GMT

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;

Re: SQL to identify the duplicates

David_Billa — Tue, 18 Apr 2023 12:21:04 GMT

@MayurJadhav I've got the error

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

Re: SQL to identify the duplicates

Tom — Tue, 18 Apr 2023 12:43:33 GMT

@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.

Re: SQL to identify the duplicates

David_Billa — Tue, 18 Apr 2023 12:46:29 GMT

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.

Re: SQL to identify the duplicates

Kurt_Bremser — Tue, 18 Apr 2023 12:59:19 GMT

@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.

Re: SQL to identify the duplicates

David_Billa — Tue, 18 Apr 2023 13:04:29 GMT

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

Re: SQL to identify the duplicates

Kurt_Bremser — Tue, 18 Apr 2023 13:13:07 GMT

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