12-01-2016 01:47 PM
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?
Please let me know if I can be clearer. Thank you!
12-01-2016 02:25 PM
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.
12-01-2016 04:08 PM
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?
12-01-2016 06:26 PM
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;
12-01-2016 02:28 PM - edited 12-01-2016 02:29 PM
Use Compress function for to have same syntax in Client Id records, after this output the similar/same id is easily.
'Client Id'n = Compress('Client Id'n, '-'); /*remove '-' from Client id*/