BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
;
4 REPLIES 4
LinusH
Tourmaline | Level 20
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
deleted_user
Not applicable
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.
Cynthia_sas
SAS Super FREQ
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]
deleted_user
Not applicable
Thank you Cynthia, that did it.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1560 views
  • 0 likes
  • 3 in conversation