topic Re: Where, contains, like, index, in:? in SAS Programming

Where, contains, like, index, in:?

bollibompa — Wed, 27 Apr 2016 17:56:10 GMT

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

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

Astounding — Wed, 27 Apr 2016 18:04:01 GMT

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;

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

PGStats — Wed, 27 Apr 2016 18:19:22 GMT

where column =: "A2";

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

bollibompa — Wed, 27 Apr 2016 18:25:50 GMT

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

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

Astounding — Wed, 27 Apr 2016 18:33:00 GMT

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.

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

bollibompa — Wed, 27 Apr 2016 20:10:55 GMT

Thanks for your replies!