In operator not working in PROC SQL

[SAS_inquisitive]

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;

[data_null__]

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?

[FreelanceReinh · Accepted solution]

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:.

[Haikuo]

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.

[FreelanceReinh]

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;

[SAS_inquisitive]

Perfect. It works.  Thanks !