Help using Base SAS procedures

PROC SQL FIND

Reply
N/A
Posts: 0

PROC SQL FIND

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
;
Super User
Posts: 5,430

Re: PROC SQL FIND

Posted in reply to deleted_user
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
Data never sleeps
N/A
Posts: 0

Re: PROC SQL FIND

Posted in reply to deleted_user
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.
SAS Super FREQ
Posts: 8,866

Re: PROC SQL FIND

Posted in reply to deleted_user
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]
N/A
Posts: 0

Re: PROC SQL FIND

Posted in reply to Cynthia_sas
Thank you Cynthia, that did it.
Ask a Question
Discussion stats
  • 4 replies
  • 154 views
  • 0 likes
  • 3 in conversation