SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Expression to drop the values

Reply
PROC Star
Posts: 633

Expression to drop the values

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.

 

Super User
Posts: 10,534

Re: Expression to drop the values

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,534

Re: Expression to drop the values

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Super User
Posts: 9,813

Re: Expression to drop the values

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

 

Respected Advisor
Posts: 4,779

Re: Expression to drop the values

[ Edited ]

@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%'

 

Ask a Question
Discussion stats
  • 4 replies
  • 62 views
  • 0 likes
  • 4 in conversation