It works if I use a literal string as the substring, so the folowing returns correctly;
PROC SQL;
CREATE TABLE BB As
SELECT a.fname
, a.lname
, a.display_name
, FIND( a.display_name , 'smit', 'i')
FROM aa a
;
RESULT
FNAME LNAME DISPLAY_NAME _TEMA001
Adam Smith Adam Smith 6
But does not work where the substring being searched for is a varaiable, as in
PROC SQL;
CREATE TABLE BB As
SELECT a.fname
, a.lname
, a.display_name
, FIND( a.display_name , a.lname , 'i')
FROM aa a
;
RESULT
FNAME LNAME DISPLAY_NAME _TEMA001
Adam Smith Adam Smith 0
The reference for 'FIND' states: "substring
is a character constant, variable, or expression that specifies the substring of characters to search for in string."
Which leads me to think that is can be used as in the second query, which should be finding the lname value 'Smith' in the display_name string 'Adam Smith'
It doesnt, there is nothing in the log to report an error, the function just doesnt seem to work as described in Proc Sql? None of the similar character matching SAS functions will work with a variable as the substring either.
For reference I am using EG as the query tool.