LIKE '%' does not work

[bncoxuk]

Hi,

I intend to select those cases which satisfy:

IF regno LIKE ('M%15', 'M%25', 'M%30', 'DISC') THEN OUTPUT;

However, this code is wrong.

How can I accomplish this? Thanks a lot.

[Reeza]

You can only use LIKE in a WHERE clause not an if.

[FriedEgg · Accepted solution]

The like operator is not available to the if statement only where clauses also I am quite sure it cannot be combined with the in operator to have a list of values.

[Ksharp]

An alternative way is to use perl regular expression.

IF prxmatch('/M.*15|M.*25|M.*30|DISC/',regno) THEN OUTPUT;

Ksharp

[rosietav]

For what it's worth, I found that using CASE within a PROC SQL SELECT statement worked for me. Sharing the example code in case it is useful.

PROC SQL;
CREATE TABLE SET_A AS

SELECT *, (CASE

                    WHEN FIELD LIKE 'APPL%' THEN 'A'

                    WHEN FIELD LIKE 'SYS%' THEN 'B'

                    ELSE ''

               END

               )

               AS NEW_FIELDNAME

FROM SOURCE_TABLE

WHERE OTHER_CRITERIA = 'XXX';

RUN;