topic Re: PROC SQL FIND in SAS Procedures

PROC SQL FIND

deleted_user — Mon, 19 Jan 2009 05:51:22 GMT

I am trying to compare two variables with FIND.
Should this work? It would seem despite the doco that the the substring needs to be a quoted litteral string?

Sampel code

PROC SQL;
CREATE TABLE BB As
SELECT a.fname
, a.lname
, a.display_name
, FIND( a.display_name , a.lname , 'i')

FROM aa a
;

Re: PROC SQL FIND

LinusH — Mon, 19 Jan 2009 12:46:25 GMT

I got it to work. In what way does it not work for you? Maybe you should show your LOG/OUTPUT. My example from sashelp.class:

proc sql;
select name, find(name,'ne','i')
from sashelp.class;
quit;

Partial output:

Name partialname findPos
Alfred ne 0
Alice ne 0
Barbara ne 0
Carol ne 0
Henry ne 0
James ne 0
Jane ne 3
Janet ne 3
...

/Linus

Re: PROC SQL FIND

deleted_user — Mon, 19 Jan 2009 22:44:30 GMT

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.

Re: PROC SQL FIND

Cynthia_sas — Mon, 19 Jan 2009 23:20:32 GMT

Hi:
You might consider using the TRIM function around the variable string that you are searching for. Consider the program below. Without the TRIM function, the length of ALTPIECE is $8 and 'ne ' is not found in anybody's name. SAS does not automatically trim trailing blanks for you in comparisons of this nature.

cynthia

[pre]
data class;
length wantpiece $2 altpiece $8;
set sashelp.class;
wantpiece = 'ne';
altpiece = 'ne';
fval = find(name,wantpiece,'i');
fval_alt = find(name,altpiece,'i');
fval3 = find(name,trim(altpiece),'i');
run;

ods listing;
proc print data=class;
title 'from data step';
var wantpiece altpiece name fval fval_alt fval3;
run;

proc sql;
select name, find(name,wantpiece,'i') as sfval,
find(name,altpiece,'i') as sfval_alt,
find(name,trim(altpiece),'i') as sfval3
from class;
quit;
[/pre]

Re: PROC SQL FIND

deleted_user — Mon, 19 Jan 2009 23:31:45 GMT

Thank you Cynthia, that did it.