topic Re: In operator not working in PROC SQL in SAS Programming

In operator not working in PROC SQL

SAS_inquisitive — Tue, 23 Feb 2016 20:24:28 GMT

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;

Re: In operator not working in PROC SQL

data_null__ — Tue, 23 Feb 2016 20:34:59 GMT

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?

Re: In operator not working in PROC SQL

FreelanceReinh — Tue, 23 Feb 2016 20:38:21 GMT

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

Re: In operator not working in PROC SQL

Haikuo — Tue, 23 Feb 2016 20:40:30 GMT

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.

Re: In operator not working in PROC SQL

FreelanceReinh — Tue, 23 Feb 2016 21:12:20 GMT

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;

Re: In operator not working in PROC SQL

SAS_inquisitive — Tue, 23 Feb 2016 21:22:33 GMT

Perfect. It works. Thanks !