The following PROC SQL code does not work while the DATA step works.
proc sql;
create table want as
select *
from sashelp.class
where name in: ('Al','Wi');
quit;
data want;
set sashelp.class;
where name in: ('Al','Wi');
run;
Hi @SAS_inquisitive,
The IN operator is working in PROC SQL, but PROC SQL does not support the colon operators for truncated string comparisons. There are equivalents to =:, <: etc. (EQT, LTT, ...), but I'm not aware of an equivalent to IN:.
32 GOPTIONS ACCESSIBLE;
33 proc sql;
34 create table want as
35 select *
36 from sashelp.class
37 where name in: ('Al','Wi');
_
22
200
ERROR 22-322: Syntax error, expecting one of the following: (, SELECT.
ERROR 200-322: The symbol is not recognized and will be ignored.
Did you notice this error?
Hi @SAS_inquisitive,
The IN operator is working in PROC SQL, but PROC SQL does not support the colon operators for truncated string comparisons. There are equivalents to =:, <: etc. (EQT, LTT, ...), but I'm not aware of an equivalent to IN:.
Nope. You are correct, not for 'in'.
Although there are some undocumented equivalent to data step:
EQ:, GT:, LT:, GE:, LE:, NE:
existing as
EQT, GTT, LTT, GET, LET, NET
in Proc SQL environment.
You could use the WHERE= dataset option in PROC SQL and use the IN: operator there:
proc sql;
create table want(where=(name in: ('Al','Wi'))) as
select *
from sashelp.class;
quit;
Perfect. It works. Thanks !
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.