BookmarkSubscribeRSS Feed
Obsidian | Level 7

I have thousands of rows of subject titles. I only want those contain, say "cat", in the titles. The text input has a search parameter embedded. The list table as the following advanced filter: 


( UpCase('SBJCT_TITLE'n) Contains UpCase(Concatenate(

Concatenate(' ', 'Search'p), ' ')) ) OR ( UpCase(Substring(

'SBJCT_TITLE'n, 1, GetLength('Search'p))) = UpCase(

'Search'p) ) OR ( UpCase('SBJCT_TITLE'n) EndsWith UpCase

(Concatenate(' ', 'Search'p)) )


The red is for words in the middle, the green for words at the start of the title and the blue for words at the back. However, as you can see in the below picture, it is not what i expected exactly. Titles with "cat" do show up, but words like "catherine" and "catcher" also shows up. Also, the above formula is not perfect as well; what if the title has "cats", instead of "cat"? Any help is appreciated.



Hi Matt,


As far as I can tell, concatenating spaces to a string does not work. I think this has to do with automatic cleanup of leading and trailing spaces.


You could check each word using GetWord(), but that would be very tedious (you would be doing something like:


(((UpCase(GetWord('TXT_CASE_SBJCT_TITLE'n, 1) = UpCase('Search'p))

OR ((UpCase(GetWord('TXT_CASE_SBJCT_TITLE'n, 2) = UpCase('Search'p))

OR ((UpCase(GetWord('TXT_CASE_SBJCT_TITLE'n, 3) = UpCase('Search'p))


And so on up to the number of words you want to support (possibly a lot!)


I checked to see if you could use Substring() with FindString() to retrieve the characters before and after the match, but it looks like any space characters get cleaned up when you do this. (You can't do StartsWith(' ')).


Possibly someone else can offer another workaround, but I think it may not be possible for your "cat" search to return only "cat" and not "catcher" or "bobcat" unless you do a crazy expression based on GetWord().





Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 1 like
  • 2 in conversation