The Use of the "where", "in()" and ":" in PROC SQL 1

[Jubinell]

As far as I know, the in operator and the colon operator modifier work splendidly before, during and after a datastep. What I mean is that the following three statements all work (albeit slightly differently):

data test2;
set test;
where textnumber in:('1','2');
run;

data test2;
set test (where= (textnumber in:('1','2'));
run;

data test2(where= (textnumber in:('1','2'));
set test;
run;

However, it appears that such is not the case in PROC SQL. In the 2 codes below, only the second one works.

proc sql;
create table test2 as
select * 
from test 
where textnumber in:('1','2')
;
quit;

proc sql;
create table test2 as
select * 
from test 
(where=(textnumber in:('1','2')))
;
quit;

Am I correct? And if so, why is that????


Finally, how do you make this code work:

proc sql;
create table test2 as
select *
from test (where=(textnumber in:(select longtextnumber from test0 where longtextnumber ne ' ')))
;
quit;

 

[ChrisW75]

The second SQL example works because it is basically a dataset option, which is treated exactly the same way as your second datastep example.
I thought the first one would have worked too, however a quick experiment reminded me that you can't use the : operator in an SAS/SQL where clause. You need to use "LIKE" instead.

   where textnumber like '1%'
      or textnumber like '2%'

You third SQL example that you want to make work is the bastard child of a datastep option and an SQL subquery.

proc sql;
  create table test2 as
  select *
  from test
  where textnumber in(select longtextnumber 
                      from test0 
                      where longtextnumber ne ' ')
  ;
quit;

If longtextnumber is different in some way to TEXTnumber and needs some work to make it match, preform this in the subquery...

proc sql;
  create table test2 as
  select *
  from test
  where textnumber in(select substr(longtextnumber,1,5) 
                      from test0 
                      where longtextnumber ne ' ')
  ;
quit;

or something similar should do the trick.
Don't do too much in the subquery, if the subquery is BIG it'll give you a serious hit in performance.
If you think it might be a problem, try splitting the query in 2 and joining the results in a simpler piece of code, you can sometimes get significant performance improvements like this.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[Jubinell]

Thank you Chris. You've been most helpful.