Duplicate rows

[rakeshvvv]

Dear All,

Can some one help me with output for identifying the duplicates. I am using the below code but i would like to output all the duplicates into output based on the three keys.I am thinking SQL should be good option but I am not sure of using sql.

PROC SORT DATA=MH OUT=MH1 DUPOUT=MH_DUP NODUPKEY;
BY SUBJECT MHTERM MHST;
RUN;

/* CAN SOMEONEHELP IN CORRECTING THE BELOW CODE

proc sql;

create table dup as 

select * from mh

having count(subject,mhterm,mhts) >1;

quit;

Thanks

Rakesh

[nehalsanghvi · Accepted solution]

Try this:

proc sql;
create table dup as 
select *
from mh
group by subject, mhterm, mhts
having count(*) >1;
quit;

[rakeshvvv]

Hi,

My Dataset have more then ten collumns but I would need to find duplicates for only three collumns SUBJECT, MHTERM, MHST. 

using  count(*) would check for all the collumns rather then SUBJECT, MHTERM, MHST. Can you correct me if my assumption is wrong.

[nehalsanghvi]

Count(*) counts rows, it is not checking columns. The way the query I sent works is it looks for dups by a combination of the three columns. So if there are multiple rows with the same values for SUBJECT, MHTERM, MHST, then those will be returned in the result set. If you are trying to look for dups either by SUBJECT or by MHTERM or by MHST independently of the other two columns, then it will be a different query. Can you try the query I sent and see if the output is what you need?

proc sql;
create table dup as 
select *
from mh
group by subject, mhterm, mhts /* this is grouping the data, every combination of values in these three columns will be put in a separate group */
having count(*) >1; /* count(*) simply counts rows in each group AFTER grouping is completed above */
quit;

If there is more than 1 row within each group that is created by 'group by' statement, then all those rows are considered dups and returned.

[rakeshvvv]

The query is working well for me. Now, I understood how the code is working......Thanks for the detailed explanation.

[Astounding]

There may be other possibilities, but this will work:

proc sort data=have;

by subject mhterm mhst;

run;

data want;

set have;

by subject mhterm mhst;

if first.subject=first.mhterm=first.mhst=1 then delete;

run;