topic Re: Applying Include Exclude criteria in control data set in SAS Programming

Applying Include Exclude criteria in control data set

RichardDeVen — Wed, 12 Oct 2022 11:58:33 GMT

Consider the problem of applying include and then exclude criteria that are SQL where patterns.

I am coding a query with an EXCEPT operator that is logging

NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.
NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized.

Can the same result be obtained without the notes?

Example:

data filter;
length state $1 pattern $20;
input state pattern;
datalines;
+ %a%
+ J%
- W%
- %c%
- %y
;

data names;
  set 
    sashelp.class(where=(name >='L'))
    sashelp.class(where=(name < 'L'))
  ;
  rownum + 1;
  keep name rownum;
run;

proc sql;
  create table eval as
  select name from
  ( 
      select name, rownum from names join filter on name like trim(pattern) where state = '+'
      except
      select name, rownum from names join filter on name like trim(pattern) where state = '-'
  )  
  order rownum
;

Re: Applying Include Exclude criteria in control data set

KevinScott — Wed, 12 Oct 2022 13:11:32 GMT

I am only aware of one way to do this using options nonotes before the proc sql step and then turning the notes back on after the quit statement.

options nonotes;
proc sql;
create table eval as
select name from
(
select name, rownum from names join filter on name like trim(pattern) where state = '+'
except
select name, rownum from names join filter on name like trim(pattern) where state = '-'
)
order rownum
;
quit;
options notes;

Re: Applying Include Exclude criteria in control data set

ballardw — Wed, 12 Oct 2022 14:41:20 GMT

You are getting the first note because you are not selecting Rownum on the outer Select clause before the nested "except". This is informational because it can indicate that you may expect a result different than you actually get but won't be able to tell why.

The second note is because of the join you used:

from names join filter

is a Cartesian join forcing every record in Names to be Joined with every record in Filter.

If that is not the desired behavior then you use a different join.

Again this is partially informational in case you did not intend the Cartesian join as they can be very resource intensive. Two data sets of 1000 records each would require 1,000,000 comparisons for example.