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

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

Saikiran_Mamidi — Mon, 03 Feb 2020 09:44:38 GMT

suppose i am having data below

id name$

1 a

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

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

PeterClemmensen — Mon, 03 Feb 2020 09:49:20 GMT

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;

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

ed_sas_member — Mon, 03 Feb 2020 10:45:19 GMT

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'