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
Try this:
proc sql;
create table dup as
select *
from mh
group by subject, mhterm, mhts
having count(*) >1;
quit;
Try this:
proc sql;
create table dup as
select *
from mh
group by subject, mhterm, mhts
having count(*) >1;
quit;
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.
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.
The query is working well for me. Now, I understood how the code is working......Thanks for the detailed explanation.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: