topic Re: Unusual String Comparison Problem in SAS Programming

Unusual String Comparison Problem

PaigeMiller — Thu, 13 Nov 2014 14:29:56 GMT

Suppose I have a character string in SAS variable named COL1, and other character strings in a SAS variable named COL2.

If I wanted to, for example, match the first five characters of COL1 with the first five characters of COL2, this is easy to do in a datastep or SQL. But my problem today is that I want to match the string in COL1 with the string in COL2 that has the most characters matching (and the match must be consecutive, and begin in position 1 of the string)

So, for example

COL1 COL2

AA999AAB AA999

AA999ABB AA999A

AA999B

AA999AB

The string in COL2 that has the longest match to AA999AAB in COL1 is AA999A (i.e. AA999 in COL2 is not the match I want)

The string in COL2 that has the longest match to AA999ABB in COL1 is AA999AB (i.e. AA999 and AA999A is not the match I want)

and so on

How can I do this? I have written a very convoluted looping program to get this done, but I'm hoping there is a smarter and more efficient way, using any of: data step, SQL, regular expressions, IML, whatever.

Thanks!

Re: Unusual String Comparison Problem

RW9 — Thu, 13 Nov 2014 14:56:23 GMT

Well, there is the soundex function which codes a word up. Not sure how close though. Am also thinking of arrays but puzzling over a simple technique.

Re: Unusual String Comparison Problem

Rick_SAS — Thu, 13 Nov 2014 15:00:32 GMT

I don't have an immediate answer, but SAS has some "distance between words" functions that might help. I discuss them in this article:

http://blogs.sas.com/content/iml/2011/10/05/distances-between-words/

In the article I used the SPEDIS function, which is apparently not what you want. You want a function that "rewards" exact matches at the beginning of a word. Nevertheless, here is some code that shows how to use the SPEDIS function. Perhaps it will give you a place to start. If none of the functions in the article perform the match you want, then you will have to write the function "cost function" yourself.

data A;
input COL1 $12.;
datalines;
AA999AAB
AA999ABB
;
data B;
input COL2 $12.;
datalines;
AA999
AA999A
AA999B
AA999AB
;

proc iml;

use A; read all var {col1} into x; close A;
use B; read all var {col2} into y; close B;

cost = j(nrow(x), nrow(y),.);
do i = 1 to nrow(x);
word = x;
cost[i,] = spedis(y`, word);
end;
print cost[rowname=x colname=y];

Re: Unusual String Comparison Problem

Ksharp — Thu, 13 Nov 2014 15:02:06 GMT

How about this one :


data a;
input COL1  : $20.;
cards;    
AA999AAB  
AA999ABB 
;
run; 
                             

data b;
input COL2  : $20.;
cards;  
AA999
AA999A
AA999B
AA999AB
;
run; 
proc sql;
create table want as
 select * from a,b group by col1 having compare(col1,col2,'il')=max(compare(col1,col2,'il'));
quit;

Xia Keshan

Re: Unusual String Comparison Problem

Ksharp — Thu, 13 Nov 2014 15:07:01 GMT

Rick,

OP asked the first N character matched , not the minimize spell distance . COMPARE() might be a good choice.

Xia Keshan

Re: Unusual String Comparison Problem

PaigeMiller — Thu, 13 Nov 2014 15:07:51 GMT

DING DING DING DING!!

Looks like has the answer! That's amazing in its simplicity. Thank you!

Re: Unusual String Comparison Problem

Ksharp — Thu, 13 Nov 2014 15:17:23 GMT

One thing need to remind : Is the length of COL1 is always greater than the length of COL2 ? otherwise ,you need to add abs() around it .

Re: Unusual String Comparison Problem

PaigeMiller — Thu, 13 Nov 2014 15:23:22 GMT

Ok, I was just thinking that COL1 length could be the same as COL2 length, but your statement "you need to add abs() around it" is somewhat vague ... add abs() around what?

Specifically, if COL1 also contains AA999A, I would want it to match AA999A in COL2, and because COMPARE returns a 0 when the strings are identical, the SQL needs to be modified for this case, but specifically how?

Re: Unusual String Comparison Problem

Ksharp — Thu, 13 Nov 2014 15:36:39 GMT

OK.Check this example . the output is what you need ?

I have to go to sleep now. Bye.

data a;

input COL1 : $20.;

cards;

AA999AAB

AA999ABB

;

run;

data b;

input COL2 : $20.;

cards;

AA999

AA999A

AA999B

AA999ABASD

;

run;

proc sql;

create table want as

select * from a,b group by col1 having compare(col1,col2,'il')=max(compare(col1,col2,'il'));

quit;

Message was edited by: xia keshan

Re: Unusual String Comparison Problem

PaigeMiller — Thu, 13 Nov 2014 15:44:56 GMT

No that's the wrong result, AA999ABB in COL1 should match AA999A in COL2 in this case. COL2 will be shorter than or the same length as COL1, I don't think in my situation COL2 can be longer than COL1.

But specifically, if the code is

data a;
input COL1 : $20.;
cards;
AA999AAB
AA999ABB
AA999A
;
run;

data b;
input COL2 : $20.;
cards;
AA999
AA999A
AA999B
AA999AB
;
run;

Note: I have added AA999A to COL1, then the result I want is AA999AAB and AA999ABB are matched properly by your first code example, but AA999A in COL1 is matched to AA999 in COL2 using your first code example, and I want it matched to AA999A in COL2.

Re: Unusual String Comparison Problem

PaigeMiller — Thu, 13 Nov 2014 16:40:43 GMT

Okay, I think I got it. The condition that must be included into the solution is that the length(COL1)>=length(COL2). This is easy to add in.

What is harder to adjust for is that if the two strings match (e.g. if COL1='AA999A' and COL2='AA999A'), then the COMPARE function gives us a zero as an answer, and so the MAX function in 's solution doesn't handle this case.

So, we create a function of the COL1 and COL2 values that turns the zero into a large number.

proc sql;

create table want as

select * from a,b group by col1 having

((compare(col1,col2,'il')>0)*compare(col1,col2,'il') + (compare(col1,col2,'il')=0)*999) =

max(((compare(col1,col2,'il')>0)*compare(col1,col2,'il') + (compare(col1,col2,'il')=0)*999))

and length(col1)>=length(col2);

quit;

So zeros get turned into 999 while the other results from COMPARE remain unchanged.

Here is the correct data set WANT:

Obs COL1 COL2

1     AA999A      AA999A
2     AA999AAB    AA999A
3     AA999ABB    AA999AB

Anyone got a simpler solution?

Re: Unusual String Comparison Problem

art297 — Thu, 13 Nov 2014 17:02:28 GMT

Paige,

I liked 's solution, but it didn't work on the extra test cases I added. The compare function returns a 0 if there is an exact match, otherwise it returns the first character that is different. As such, here is my proposed solution:

data have;

input (COL1 COL2) ($);

cards;

AA999AAB AA999

AA999ABB AA999A

BB999AAB AA999B

. AA999AA

. AA999AB

. AA999ABB

;

data col1;

set have (keep=col1 where=(not missing(col1)));

run;

data col2;

set have (keep=col2);

run;

proc sql;

create table want as

select *,

case compare(col1,col2,'il')

when 0 then length(col1)

else compare(col1,col2,'il')-1

end as length

from col1,col2

group by col1

having (calculated length>0) and

calculated length=max(calculated length)

;

quit;

Re: Unusual String Comparison Problem

Haikuo — Thu, 13 Nov 2014 17:05:08 GMT

Your requirement still seems a little vague to me, will COL2 have to inclusive to COL1 if matched? Will they always compare from the first char? If both Yes, then try the following:

data a;

input COL1 : $20.;

cards;

AA999AAB

AA999ABB

AA999A

;

run;

data b;

input COL2 : $20.;

cards;

AA999

AA999A

AA999B

AA999AB

;

run;

proc sql;

create table want as

select col1, (select distinct col2 from b where col1 eqt trim(col2) having length(col2) = max(length(col2))) as col2

from a;

quit;

Good luck,

Haikuo

Re: Unusual String Comparison Problem

Astounding — Thu, 13 Nov 2014 17:23:01 GMT

Lack of SQL skills would force me to take a totally different approach.

Step 1: Create a format that translates from the value of COL2 to the observation number holding that value.

Step 2: Loop through COL1 (all characters down to 1 character) until you find a match.

For example, hard-coding the format just for readability step 1 would look like this:

proc format;
value $recno

'AA999'='1'

'AA999A'='2'

'AA999B'='3'

'AA999AB'='4'

other='Not Found';

run;

In practice, I'm sure you would want to switch to a CNTLIN= data set.

Then step 2 uses the format:

data want;

set col1_source;

if COL1 > ' ' then do _n_=length(COL1) to 1 by -1 until (result ne 'Not Found');

result = put(substr(COL1, 1, _n_), $recno.);

end;

if result ne 'Not Found' then do;

recno = input(result, 8.);

set col2_dataset point=recno;

end;

run;

It's sophisticated, but not horribly complex.

Good luck.

Re: Unusual String Comparison Problem

PaigeMiller — Thu, 13 Nov 2014 17:23:42 GMT

Your requirement still seems a little vague to me, will COL2 have to inclusive to COL1 if matched?

Here are the requirements. The desired output is when COL1 and COL2 have the same characters in positions 1 through n, where n is the maximum value of all comparisons of each COL1 with all COL2; and length(COL1)>=length(COL2)

Re: Unusual String Comparison Problem

PaigeMiller — Thu, 13 Nov 2014 17:25:28 GMT

Yes, . I originally had a loop solution, which does work, but in any event, the reason for my question was to see if simpler methods are available (and also hoping that I would learn something, which has happened!)

Re: Unusual String Comparison Problem

art297 — Thu, 13 Nov 2014 17:34:22 GMT

Paige,

Did you try the code I had suggested? It gives the same results as your code but, additionally, excludes cases where there is no match.

Art

Re: Unusual String Comparison Problem

PaigeMiller — Thu, 13 Nov 2014 17:40:57 GMT

Seem to work, , thank you!

Re: Unusual String Comparison Problem

Haikuo — Thu, 13 Nov 2014 17:42:45 GMT

Paige,

Forgive me if I overlooked your comments, I am aware of that the length of COL2 is less or equal to COL1, but does COL2 have to be inclusive to COL1? For instance, consider the following examples:

for COL1=AA999AAB,

COL2 of AA999 is a better match or AA999AB? notice the latter has the bigger n? if it is the latter, then what is your rule of dealing ties? eg. AA999AB or AA999AC?

Thanks,

Haikuo

Re: Unusual String Comparison Problem

PaigeMiller — Thu, 13 Nov 2014 17:51:16 GMT

Hai.kuo wrote:

for COL1=AA999AAB,

COL2 of AA999 is a better match or AA999AB?

For the case above, COL2 of AA999 is the match I want, as the requirement seems to have been incompletely stated. When you match COL1 and COL2, there cannot be any mismatches, positions 1 thru length(COL2) must match, and I want the longest COL2 which has this property. If you match COL1=AA999AAB with COL2=AA999AB, there is a mismatch in postion 7, so it is disqualified.