topic Re: Expression to drop the values in SAS Data Management

Expression to drop the values

Babloo — Mon, 13 Aug 2018 07:39:18 GMT

I want to drop the values if the variable 'KHIREF' begins with '003'. It has following values.

002134

003423

001342

So if I put 'KHIREF not in '003' under expression in SAS DI across that variable I'm getting syntax error as mentioned below. Could you please guide me to resolve the issue?

2102                (KHIREF not in '003') as KHIREF length = 20
                                   _____
                                   22
                                   76
ERROR 22-322: Syntax error, expecting one of the following: (, SELECT.  

ERROR 76-322: Syntax error, statement will be ignored.

Re: Expression to drop the values

Kurt_Bremser — Mon, 13 Aug 2018 08:10:26 GMT

An "in" list needs to be enclosed in brackets; if you only have a single value, use = instead of in.

Re: Expression to drop the values

Kurt_Bremser — Mon, 13 Aug 2018 08:11:55 GMT

And use the substr() function if you only need to compare with a substring.

Re: Expression to drop the values

RW9 — Mon, 13 Aug 2018 08:15:45 GMT

Really?

where substr(khiref,1,3) ne "003";

Do note you can't use

(KHIREF not in '003') as KHIREF

What are you trying to do here (this is where test data/output wanted would come in handy!!). Perhaps you need a case when:

case when substr(khiref,1,3) ne "003" then khiref else "" end as khiref

Re: Expression to drop the values

Patrick — Mon, 13 Aug 2018 22:07:47 GMT

@Babloo

This belongs into the Where clause and not the column expression.

In the where clause use a LIKE operator. Once set-up check the DIS generated code. You need to see something like:

where not khiref like '003%'