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 !
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.