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.
An "in" list needs to be enclosed in brackets; if you only have a single value, use = instead of in.
And use the substr() function if you only need to compare with a substring.
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
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%'
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.
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.