Search String

[nickdel]

Using SAS v8 so unfortunately I cannot use PRXNEXT. What I'm basically looking to do is create some sort of string search function so that I can break down an input string and then compare this to a column on a table returning the most likely matches.

Has anyone done anything similar?

Thanks

Nick

 

[klaz2002]

I do this all the time. Use the index & indexw functions. See the sas help for more info on those functions. If you do not know the column names in advance you can use the array and their auto options. (_character_, _Numeric_) These two keywords put all the character vars into an array (or for numeric, the numeric columns). Then when you have a match you could use the vname() function to get the column name for further coding needs.

If you map out a task that you need completed perhaps I can give you more specific tips.
Klaz

 

[nickdel]

Thanks Klaz, basically I have been given a list of company names which I need to map to a table in another database however there is no unique identifier for the 2. So for example I could be given a list like so:

Joe Bloggs corp
John Smith ltd

I then need to try and find the best match from a table which lists these companies but unfortunately they are not always set out the same. For example it could be stored on the table as "Bloggs, Joe corp" or "John Smith Limited" so what I need to do (unless there is another way) is to break down the input string "Joe" + "Bloggs" + "corp" and then compare each of these values to the company in the table to give me a score for the best possible matches.

I'm probably over complicating things but I really cannot think of another way to do this!

Nick

 

[nickdel]

Ok, think I may have got this albeit it's probably a bit messy but here's the code anyway:

%macro words(str,delim=%str( ));

%local i;

%let i=1;

%do %while(%length(%qscan(&str,&i,&delim)) GT 0);
  %let i=%eval(&i + 1);
%end;

%eval(&i - 1)

%mend;




data SearchString;
	set account;
	keep AccountName Score InputStr words;
	InputStr='Jarvis Hotels Ltd';

	%let wordcount=%words(InputStr); /*find the number of words in the string*/

	words=&wordcount;

	array InStr(&wordcount) $ ;
	VariableScan=AccountName;
	Score=0;

	do i = 1 to &wordcount;
		Instr(i)=scan(InputStr,i,' ');
		WordPosition=indexw(VariableScan,Instr(i));
			if WordPosition > 0 then do;
				Score = Score+1;
			end;
	end;

if Score > 1 then output;

run;

 

[nickdel]

little amendment:

%let wordcount=%words(InputStr);

should be:

 %let wordcount=%words("'" InputStr "'");

 

[ChrisW75]

The Scan function is also extremely handy, and from memory doesn't generate errors when you ask it to search beyond the end of the text.

  scan(variable,<word number>,<word delimiter>);

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[nickdel]

Thanks for the input guys, it's starting to work for me now. I do however have one question...

In my code above I compare an Input string ("Jarvis Hotels Ltd") to a column on a dataset. How can I make the input string a variable from another dataset. i.e. when it's finished checking for "Jarvis..." move to the next one in the dataset and check this against the account dataset again?

 

[tweaked2005]

I just want to thank everyone who contributed to this string. It helped me on a very similar issue. Just a reminder that this forum is beneficial to not only the original poster.

Thanks all!