SAS Index function - Searching one column using values from another

Question

I'm running into an issue when using the index function to return the position of a string within a string.

So I have a concentrated list of surnames:

SMITH|JONES|DONALD

And I want to find the position of a string referenced from another column (SurnametoFind), so:

index(Surnames,'SMITH')

Works perfectly, however, when I reference the column I want to use the value to search on from (a list of single surnames which contains SMITH in one of the rows):

index(Surnames,SurnametoFind)

Returns 0.

This is within an SQL proc, so I also attempted with wildcards (in case index functions like PATINDEX on SQL Server):

index(Surnames,'%'||SurnametoFind||'%')

But this also returns zero.

Am I missing something about how the SurnametoFind column should be referenced in the index function?

Many thanks.

Tom · Accepted Answer · 2019-10-31 13:46:14Z

1

The main problem is that you have not accounted for the fact that SAS stores character variables as fixed length. So if SurnametoFind is long enough to store "DONALD" then when the value is "SMITH" there is at least one space after the H. So the string "SMITH " is not found in the string "SMITH|JONES|DONALD".

You can use the TRIM() function to remove the trialing spaces.

index(Surnames,trim(SurnametoFind))

But since your list is delimited you probably want to use the FINDW() function instead. With that you can tell it what character(s) delimit the "words" in the string that is being searched. It also has modifiers that will let you tell it to do the trimming (and other features) for you.

findw(Surnames,SurnametoFind,'|','t')

answered Oct 31, 2019 at 13:46

Tom

52.3k2 gold badges19 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lorielus Over a year ago

Thanks a lot - that solved it, much appreciated - and definitely a good shout on switching it to use findw.

data _null_ · Accepted Answer · 2019-10-31 13:45:46Z

0

You need to trim the blanks from SurnametoFind. Or use FIND function with T option may want to use I option also.

1    data _null_;
2       input Surnames :$20. SurnametoFind:$20.;
3       put (s:)(=);
4       x1 = index(surnames,surnametofind);
5       x2 = index(surnames,trim(surnametofind));
6       x3 = find(surnames,surnametofind,'IT');
7       put (x:)(=);
8       cards;

Surnames=SMITH|JONES|DONALD SurnametoFind=SMITH
x1=0 x2=1 x3=1
Surnames=SMITH|JONES|DONALD SurnametoFind=Smith
x1=0 x2=0 x3=1

answered Oct 31, 2019 at 13:45

data _null_

9,12914 silver badges16 bronze badges

Collectives™ on Stack Overflow

SAS Index function - Searching one column using values from another

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related