DATA Step, Macro, Functions and more

In operator not working in PROC SQL

Accepted Solution Solved
Reply
Regular Contributor
Posts: 234
Accepted Solution

In operator not working in PROC SQL

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;

 

 


Accepted Solutions
Solution
‎02-23-2016 04:08 PM
Trusted Advisor
Posts: 1,115

Re: In operator not working in PROC SQL

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

View solution in original post


All Replies
Respected Advisor
Posts: 3,777

Re: In operator not working in PROC SQL

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?

Solution
‎02-23-2016 04:08 PM
Trusted Advisor
Posts: 1,115

Re: In operator not working in PROC SQL

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

Respected Advisor
Posts: 3,124

Re: In operator not working in PROC SQL

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.

 

 

Trusted Advisor
Posts: 1,115

Re: In operator not working in PROC SQL

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;
Regular Contributor
Posts: 234

Re: In operator not working in PROC SQL

Perfect. It works.  Thanks !

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 344 views
  • 4 likes
  • 4 in conversation