DATA Step, Macro, Functions and more

Duplicate/Close Match values across records

Reply
Frequent Contributor
Posts: 131

Duplicate/Close Match values across records

Hi there,

Would someone have ideas on how to determine if there are similar looking values for a variable across records?

For example, in the following sample dataset (one variable), is there a series of steps/ a function to output if there is a similar looking client ID in the rest of the dataset and, if so, what it is?

 

Client ID

ABC123

ABC-123

DEF123

DEF-123

 

Please let me know if I can be clearer. Thank you!

 

 

 

Respected Advisor
Posts: 4,925

Re: Duplicate/Close Match values across records

Posted in reply to Maisha_Huq

It depends what the issue really is. If added characters are the problem, then you simply need to remove them (look at the COMPRESS function). If the problem is spelling variations then look at spelling distance functions (SPEDIS, COMPLEV, COMPGED). For a phonetic-based comparison, look at SOUNDEX.

PG
Frequent Contributor
Posts: 131

Re: Duplicate/Close Match values across records

Thank you, PG Stats.  But wouldn't these functions such as the spelling distance functions help me determine the spelling distance between two observations within one record instead of determining the spelling distance between two observations across records but within one variable?

Super User
Posts: 11,343

Re: Duplicate/Close Match values across records

Posted in reply to Maisha_Huq

Here is one very simplistic approach to see what might be done. Note that if you have a large data set this can take time. If there are duplicate values that may be in the data you likely should reduce to one record of each value. Also you will get A <-> B and B <-> result pairs. An exercise for the interested reader is reducing those to one side only.

 

data have;
   input ClientID $;
datalines;
ABC123
ABC-123
DEF123
DEF-123
;
run;

proc sql;
   create table spelldist as
   select a.clientid, b.clientid as OtherVal,
          compged(a.clientid,b.clientid) as Compdist,
          spedis(a.clientid,b.clientid) as spedist
   from have as a, have as b
   where a.clientid ne b.clientid
   ;
quit;
Regular Learner
Posts: 1

Re: Duplicate/Close Match values across records

[ Edited ]
Posted in reply to Maisha_Huq

Hi,

 

Use Compress function for to have same syntax in Client Id records, after this output the similar/same id is easily.

 

Data TableName;

set TableName;

'Client Id'n = Compress('Client Id'n, '-'); /*remove '-' from Client id*/

run;

Ask a Question
Discussion stats
  • 4 replies
  • 197 views
  • 0 likes
  • 4 in conversation