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 !
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.