topic Re: Duplicate rows in SAS Programming

Duplicate rows

rakeshvvv — Mon, 27 Feb 2017 18:09:23 GMT

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

Re: Duplicate rows

nehalsanghvi — Mon, 27 Feb 2017 18:22:38 GMT

Try this:

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

Re: Duplicate rows

Astounding — Mon, 27 Feb 2017 18:33:41 GMT

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;

Re: Duplicate rows

rakeshvvv — Mon, 27 Feb 2017 18:38:15 GMT

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.

Re: Duplicate rows

nehalsanghvi — Mon, 27 Feb 2017 18:54:01 GMT

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.

Re: Duplicate rows

rakeshvvv — Mon, 27 Feb 2017 19:38:16 GMT

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