how to find duplicate records in output dataset? only proc sql?

[Saikiran_Mamidi]

suppose i am having data below

id    name$

1      a

1      a

2      b

2      b

3      c

4      d

Here i want duplicate records by using proc sql in output dataset directly?

i.e       id    name$

            1     a

             2    b

code and explanation required?hightly appreciated...thanks

[PeterClemmensen]

Is it a strick requirement to do this in SQL?

data have;
input id name $;
datalines;
1 a
1 a
2 b
2 b
3 c
4 d
;

proc sql;
    create table want as
    select * from have
    group by id, name
    having count(*) gt 1;
quit;

[ed_sas_member]

Hi @Saikiran_Mamidi 

In addition to PROC SQL, you can also easily do that using a PROC SORT with the NODUPKEY option.

You can output:

- a dataset with no duplicate records (out = )

- a dataset with duplicate records (dupout = )

proc sort data=have out=no_dupkey dupout=dupkey nodupkey;
	by _all_;
run;

Dataset 'no_dupkey'Dataset 'dupkey'