BookmarkSubscribeRSS Feed
Maisha_Huq
Quartz | Level 8

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!

 

 

 

4 REPLIES 4
PGStats
Opal | Level 21

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
Maisha_Huq
Quartz | Level 8

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?

ballardw
Super User

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;
Flexron
Calcite | Level 5

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 971 views
  • 0 likes
  • 4 in conversation