topic Re: Duplicate/Close Match values across records in SAS Programming

Duplicate/Close Match values across records

Maisha_Huq — Thu, 01 Dec 2016 18:47:27 GMT

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!

Re: Duplicate/Close Match values across records

PGStats — Thu, 01 Dec 2016 19:25:46 GMT

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.

Re: Duplicate/Close Match values across records

Flexron — Thu, 01 Dec 2016 19:29:43 GMT

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;

Re: Duplicate/Close Match values across records

Maisha_Huq — Thu, 01 Dec 2016 21:08:14 GMT

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?

Re: Duplicate/Close Match values across records

ballardw — Thu, 01 Dec 2016 23:26:07 GMT

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;