DATA Step, Macro, Functions and more

Where, contains, like, index, in:?

Accepted Solution Solved
Reply
Contributor
Posts: 73
Accepted Solution

Where, contains, like, index, in:?

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

Re: Where, contains, like, index, in:?

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.

View solution in original post


All Replies
Super User
Posts: 5,076

Re: Where, contains, like, index, in:?

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;

Contributor
Posts: 73

Re: Where, contains, like, index, in:?

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

Re: Where, contains, like, index, in:?

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.

Contributor
Posts: 73

Re: Where, contains, like, index, in:?

Thanks for your replies!

Respected Advisor
Posts: 4,641

Re: Where, contains, like, index, in:?

where column =: "A2";

PG
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 329 views
  • 1 like
  • 3 in conversation