topic Re: Miss Match data from same dataset (get duplicates) in SAS Programming

Miss Match data from same dataset (get duplicates)

sas_lak — Tue, 15 Jul 2014 09:46:24 GMT

Hi I am Sorry for small correction in data.

looking the duplicate data within the same dataset by the different fields

Ex:-

data have;

input name $5.+1 phone $5.;

cards;

AAAAA 12345

AAAAC 12345

AAAAA 09876

BBBBB 12345

CCCCC 99999

DDDDE 88888

DDDDD 88888

DDDDD 55555

EEEEE 44444

FFFFF 11111

;

RUN;

Am looking duplicate phone numbers by Name.(Same name having different Phone numbers)

Ex:-

Name Phone

AAAAA 12345

AAAAA 09876

DDDDE 88888

DDDDD 55555

I think we can use Soundex function to get exact duplicate values by proc sql;

plz any one help me with syntax.

Would Appreciate in Advance.

Re: Miss Match data from same dataset (get duplicates)

Chrishi — Tue, 15 Jul 2014 09:57:51 GMT

Hope this solves your issue,

proc sql;

create table want as select *,count(name) as count from have group by name having count > 1;

quit;

Sudeer

Re: Miss Match data from same dataset (get duplicates)

RW9 — Tue, 15 Jul 2014 10:01:57 GMT

Hi,

data have;
attrib Name Phone format=$200.;
name="AAA"; phone="12345"; output;
name="AAA"; phone="67890"; output;
name="BBB"; phone="fssfs"; output;
run;

proc sql;
     create table WANT as
     select     *
     from       HAVE
     group by NAME
     having    count(name)>1;
quit;

Re: Miss Match data from same dataset (get duplicates)

sas_lak — Tue, 15 Jul 2014 10:42:37 GMT

Thnank you for your valuable information

I Think we can use SOUNDEX Function to get exact Duplicates values.

proc sql;

create table dups as select distinct a.*, b.*

from have as a,have as b

where soundex(a.name) eq soundex(b.name) and a.phone ne b.phone

order by a.name

;

quit;

Re: Miss Match data from same dataset (get duplicates)

user24feb — Tue, 15 Jul 2014 11:06:58 GMT

less elegant, but if you don´t care to sort and merge:

Proc Sort Data=have;
By name;
Run;

Proc Freq Data=Have NoPrint;
Tables name / Out=Have_F (Keep=Count name);
Run;

Data Want (Where=(Count ge 2));
Merge Have (in=inH) Have_F (in=inF);
By Name;
If inH;
Run;

Re: Miss Match data from same dataset (get duplicates)

Chrishi — Tue, 15 Jul 2014 11:25:59 GMT

proc sql;

create table want as select * from have group by name having count > 1;

quit;

proc sort data=want out=dup nodupkey;

by name phone;

run;

Sudeer

Re: Miss Match data from same dataset (get duplicates)

Ksharp — Tue, 15 Jul 2014 13:50:56 GMT

Still not full sure what you try to get.

data have;
input name $5.+1 phone $5.;
cards;
AAAAA 12345
AAAAC 12345
AAAAA 09876
BBBBB 12345
CCCCC 99999
DDDDE 88888
DDDDD 88888
DDDDD 55555
EEEEE 44444
FFFFF  11111
;
RUN;
proc sql;
select * from have group by name having count(distinct phone) ge 2;
quit;

Xia Keshan