turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Where, contains, like, index, in:?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-27-2016 01:56 PM

Hi,

I have a column which contains different codes separated by a space:

Column A

A20B3 B35XX Z70A2 A22VV

C30B3 B35XX Z70A2 C34VV

C30B3 B35XX Z70A2 A24VV

I now like to keep the rows where this column include codes **beginning** with A2

If I use "where column A contains A2" the two first rows will be kept which is uncorrect since no code in row 2 start with A2.

If I use "where column A in:A2" it will keep the first one, but the third row also have a code beggining with A2 which I want to keep.

Thanks for any help to solve this!

Thomas

Accepted Solutions

Solution

04-27-2016
04:10 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-27-2016 02:33 PM

No, you would really have to repeat the search:

where (index(' ' || columnA, ' A2') > 0) or (index(' ' || columnA, ' C2') > 0);

The more you add, the closer to get to having to learn parsing functions instead.

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-27-2016 02:04 PM

The trick to solving this easily and quickly (not searching every word in the string, avoiding parsing functions) is to append a blank at the beginning:

where index(' ' || columnA, ' A2') > 0;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-27-2016 02:25 PM

Thanks!

Could I modify this index-argument if I want in the same search select several codes for example, codes that starts with A2 and C2?

/Thomas

Solution

04-27-2016
04:10 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-27-2016 02:33 PM

No, you would really have to repeat the search:

where (index(' ' || columnA, ' A2') > 0) or (index(' ' || columnA, ' C2') > 0);

The more you add, the closer to get to having to learn parsing functions instead.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-27-2016 04:10 PM

Thanks for your replies!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-27-2016 02:19 PM

where column =: "A2";

PG