BookmarkSubscribeRSS Feed
Babloo
Rhodochrosite | Level 12

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.

 

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

 

Patrick
Opal | Level 21

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

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 990 views
  • 0 likes
  • 4 in conversation