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 !
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.