topic Re: deleting mixed duplicates in SAS Programming

deleting mixed duplicates

jfaruqui — Mon, 05 Nov 2018 19:13:34 GMT

I have a dataset which has a number of variables including NAME.

I am trying to delete duplicate observations of name where one observation would be 'John Smith' and another observation would be 'smith john' .. they are clearly the same person and i want to delete the duplicate entry .. what would be the most efficient way to do it ?

considering also that the duplicate names could occur anywhere within the dataset.

Ex:

John Smith

Cal Harper

freddy Holt

smith john

frank waters

harper Cal

deleting duplicates

jfaruqui — Mon, 05 Nov 2018 18:50:01 GMT

I have a dataset which has a number of variables including NAME.

Re: deleting duplicates

PeterClemmensen — Mon, 05 Nov 2018 18:56:29 GMT

How do you determine a duplicate then? What about "Johnn Smith"?

Perhaps the COMPLEV or COMPGED Function can be of help. These compute 'distances' between strings.

Re: deleting duplicates

novinosrin — Mon, 05 Nov 2018 19:06:19 GMT

data have;
length name $50;
name='smith john';
output;
name='John smith';
output;
name='Mcdonald John';
output;
name='John Mcdonald';
output;
run;

data t;
set have;
array t(50) $1 ;
call pokelong(compress(upcase(name)),addrlong(t(1)),50);
call sortc(of t(*));
w=cats(of t(*));
drop t:;
run;
proc sort data=t out=want(drop=w) nodupkey;
by w;
run;

Re: deleting duplicates

novinosrin — Mon, 05 Nov 2018 19:15:09 GMT

With temporary array,

data t;
set have;
array t(50) $1 _temporary_;
call missing(of t(*));
call pokelong(compress(upcase(name)),addrlong(t(1)),50);
call sortc(of t(*));
w=cats(of t(*));
run;
proc sort data=t out=want(drop=w) nodupkey;
by w;
run;

Re: deleting duplicates

jfaruqui — Mon, 05 Nov 2018 19:19:00 GMT

would this work even if the duplicates were far apart within the dataset ?? separated by many unique observations ?

Re: deleting mixed duplicates

novinosrin — Mon, 05 Nov 2018 19:19:15 GMT

I responded the same question in your other thread

https://communities.sas.com/t5/SAS-Programming/deleting-duplicates/m-p/510553/highlight/false#M137396

data have;
input name $50.;
cards;
John Smith
Cal Harper
freddy Holt
smith john
frank waters
harper Cal
;
run;

data t;
set have;
array t(50) $1 _temporary_;
call missing(of t(*));
call pokelong(compress(upcase(name)),addrlong(t(1)),50);
call sortc(of t(*));
w=cats(of t(*));
run;
proc sort data=t out=want(drop=w) nodupkey;
by w;
run;

Re: deleting duplicates

novinosrin — Mon, 05 Nov 2018 19:20:01 GMT

rather than sentences, can you give a good representative sample of what you have plz

Re: deleting mixed duplicates

novinosrin — Mon, 05 Nov 2018 19:23:12 GMT

I don't know how to merge the threads although I could request @Reeza / @Kurt_Bremser to help merge the duplicate threads

Going forward, Kindly edit in the same thread you started plz

Re: deleting mixed duplicates

jfaruqui — Mon, 05 Nov 2018 19:26:09 GMT

thanks bro .. sorry for the duplicate threads .. unfortunately I can't test your code on my dataset in SAS University Edition as I am getting these error messages:

ERROR: The function POKELONG cannot be invoked when SAS is in the lockdown state.
ERROR: The function ADDRLONG cannot be invoked when SAS is in the lockdown state.
ERROR 251-185: The subroutine POKELONG is unknown, or cannot be accessed. Check your spelling.
Either it was not found in the path(s) of executable images, or there was incorrect or missing subroutine descriptor
information.

ERROR 68-185: The function ADDRLONG is unknown, or cannot be accessed.

But i am sure this solution would give the required result ... Thanks again bro !!

Re: deleting mixed duplicates

novinosrin — Mon, 05 Nov 2018 19:28:15 GMT

Hang on, if you are new/relatively new to SAS let alone APP, I beg your pardon, ignore the use of APP data management functions.

Re: deleting mixed duplicates

jfaruqui — Mon, 05 Nov 2018 19:32:46 GMT

So new/relatively new that this is first time I heard about APP functions .. fascinating stuff though .. reading about it just now ..

Re: deleting mixed duplicates

novinosrin — Mon, 05 Nov 2018 19:37:39 GMT

Ok, just try the 32 bit version-->

data t;
set have;
array t(50) $1 _temporary_;
call missing(of t(*));
call poke(compress(upcase(name)),addr(t(1)),50);
call sortc(of t(*));
w=cats(of t(*));
run;
proc sort data=t out=want(drop=w) nodupkey;
by w;
run;

Test this and see if this works

Re: deleting mixed duplicates

jfaruqui — Mon, 05 Nov 2018 19:39:16 GMT

Sorry Buddy 🙂
Same errors as before ..
I guess SAS University Edition is quite restrictive and doesn't allow access to memory .. POKE won't work either ... but I shall persevere .. highly obliged and grateful for your time and engagement to my query ..

Re: deleting mixed duplicates

novinosrin — Mon, 05 Nov 2018 19:44:03 GMT

@jfaruqui Ok Lets go linear


data have;
input name $50.;
cards;
John Smith
Cal Harper
freddy Holt
smith john
frank waters
harper Cal
;
run;


data t;
set have;
array t(50) $1 _temporary_;
call missing(of t(*));
n=compress(upcase(name));
do _n_=1 to length(n);
t(_n_)=char(n,_n_);
end;
call sortc(of t(*));
w=cats(of t(*));
run;
proc sort data=t out=want(drop=w n) nodupkey;
by w;
run;

Re: deleting mixed duplicates

jfaruqui — Mon, 05 Nov 2018 19:47:07 GMT

BINGO !!
Awesome and thank you so very much !!

Re: deleting duplicates

Reeza — Mon, 05 Nov 2018 21:22:24 GMT

Since this was answered in your other thread, I'm going to merge them.

Re: deleting mixed duplicates

FreelanceReinh — Mon, 05 Nov 2018 23:53:57 GMT

Minor variant of @novinosrin's solution (sorting the names rather than the letters contained in the names) and more test data:

data have;
input name $50.;
cards;
Sylvie Elpers
Lissy Pleever
Elvis Presley
Lily Spreeves
Presley Elvis
;

data t;
set have;
array t(5) $25 _temporary_;
length w $50;
call missing(of t(*));
n=compbl(upcase(name));
do _n_=1 to countw(n,' ');
  t(_n_)=scan(n,_n_,' ');
end;
call sortc(of t(*));
w=catx(' ', of t(*));
run;

proc sort data=t out=want(drop=w n) nodupkey;
by w;
run;

Re: deleting mixed duplicates

jfaruqui — Tue, 06 Nov 2018 09:04:59 GMT

As a matter of fact this solution is more relevant because, as you rightly demonstrated above, different unique names can still contain the same letters .. if we sort by letters then the entire dataset will be eliminated except 1 name !! So yes, this solution is more robust

Re: deleting mixed duplicates

goravnet — Fri, 23 Nov 2018 16:28:11 GMT

Hello..

I am pretty new to SAS.. I found solution good for most of cases but it will fail for the scenarios where two different names have same set of characters like mentioned below..

Example:
Raam Rahim
Ram Rahima
Rahim Raam
Rama Rahim

Output should be 3 names but code is generating just one output so i don't think so we can use character sorting to compare and probably we need to compare word by word.