DATA Step, Macro, Functions and more

In operator not working in PROC SQL

Accepted Solution Solved
Reply
Super Contributor
Posts: 271
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,118

Re: In operator not working in PROC SQL

Posted in reply to SAS_inquisitive

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,799

Re: In operator not working in PROC SQL

Posted in reply to SAS_inquisitive
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,118

Re: In operator not working in PROC SQL

Posted in reply to SAS_inquisitive

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,156

Re: In operator not working in PROC SQL

Posted in reply to SAS_inquisitive

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,118

Re: In operator not working in PROC SQL

Posted in reply to SAS_inquisitive

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;
Super Contributor
Posts: 271

Re: In operator not working in PROC SQL

Posted in reply to FreelanceReinhard

Perfect. It works.  Thanks !

☑ This topic is solved.

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

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