DATA Step, Macro, Functions and more

Duplicate rows

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 145
Accepted Solution

Duplicate rows

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


Accepted Solutions
Solution
‎02-27-2017 02:38 PM
Frequent Contributor
Posts: 75

Re: Duplicate rows

Try this:

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

View solution in original post


All Replies
Solution
‎02-27-2017 02:38 PM
Frequent Contributor
Posts: 75

Re: Duplicate rows

Try this:

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

Re: Duplicate rows

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.

Frequent Contributor
Posts: 75

Re: Duplicate rows

[ Edited ]

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.

 

Frequent Contributor
Posts: 145

Re: Duplicate rows

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

Super User
Posts: 5,082

Re: Duplicate rows

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 224 views
  • 1 like
  • 3 in conversation