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!
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.
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?
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.