BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rakeshvvv
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
nehalsanghvi
Pyrite | Level 9

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

5 REPLIES 5
nehalsanghvi
Pyrite | Level 9

Try this:

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

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
Pyrite | Level 9

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
Quartz | Level 8

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

Astounding
PROC Star

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;

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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