topic Re: Using a WHERE AND in SAS Programming

Using a WHERE AND

KC_16 — Wed, 20 Nov 2019 14:52:35 GMT

Hi,

I have the code below which doesn't quite work as the second part of the WHERE clause, I still get a mixture of results and not just the ones stated, any idea why please? Within my first part of WHERE, I only get New and Old as stated, but in my second part, I get additional outputs such as New, Old, returned, faulty etc

proc sql;
create table work.REPORT_TEST as
select
orderid,
Order_thisMonth,
order_lastMonth
from work.trans_&curmnth.
where order_lastMonth= 'new' or order_lastMonth= 'old'
and Order_thisMonth= 'new' or Order_thisMonth= 'old';
quit;

Re: Using a WHERE AND

PeterClemmensen — Wed, 20 Nov 2019 14:57:28 GMT

I can't see your data so needless to say, this is untested. But I think the problem is missing parenthesis around your conditions. SAS evaluates AND before OR, which I think causes the error

Try this

proc sql;
create table work.REPORT_TEST as
select
orderid,
Order_thisMonth,
order_lastMonth
from work.trans_&curmnth.
where (order_lastMonth= 'new' or order_lastMonth= 'old')
and (Order_thisMonth= 'new' or Order_thisMonth= 'old');
quit;

Re: Using a WHERE AND

Kurt_Bremser — Wed, 20 Nov 2019 15:00:30 GMT

The logical AND operator is evaluated before logical OR.

So your condition is effectively

where
  order_lastMonth = 'new' or
  (order_lastMonth = 'old' and Order_thisMonth = 'new') or
  Order_thisMonth = 'old'
;

PS not only SAS does that, it is a basic rule of mathematical logic and always handled like this.

Re: Using a WHERE AND

Cynthia_sas — Wed, 20 Nov 2019 15:37:24 GMT

Hi:

I'm having trouble envisioning your data and what you want to get. Let's take some fake data that has your 3 variables in different combinations. Here's some fake data:

Given your current WHERE clause, it's hard to understand whether you're going to get what you want, since you don't have any parentheses to clarify how the AND logical operator and the OR logical operator should be treated (which one gets precedence). For a concrete example, which of the above 9 rows do you expect your WHERE to select? If I run your original program without parentheses in the WHERE against the above data, THIS is what I get:

Is that what you expect given the fake data?

Here's some code to test with my fake data.


data trans_Nov;
  infile datalines dlm=',';
  input orderid order_thismonth $ order_lastmonth $;
datalines;
11,new,new
12,new,old
13,old,new
14,old,old
15,aaa,bbb
16,new,ccc
17,old,ddd
18,eee,new
19,fff,old
;
run;
   
proc print data=work.trans_nov;
  title 'What is in Fake data?';
run;
    
proc sql;
create table work.REPORT_TEST1 as
select orderid, Order_thisMonth, order_lastMonth
from work.trans_nov
where order_lastMonth= 'new' or order_lastMonth= 'old'
and Order_thisMonth= 'new' or Order_thisMonth= 'old';
quit;
 
proc print data=work.report_test1;
  title '1) AFTER report_test1 no parentheses';
run;
 
proc sql;
create table work.REPORT_TEST1b as
select orderid, Order_thisMonth, order_lastMonth
from work.trans_nov
WHERE (order_lastMonth='new') or 
      ((order_lastMonth='old') and (Order_thisMonth='new')) or 
      (Order_thisMonth='old');
quit;
 
proc print data=work.report_test1b;
  title '1b) AFTER report_test1b possible parentheses';
run;
 
proc sql;
create table work.REPORT_TEST2 as
select orderid, Order_thisMonth, order_lastMonth
from work.trans_nov
where (order_lastMonth= 'new' or order_lastMonth= 'old')
       and 
      (Order_thisMonth= 'new' or Order_thisMonth= 'old');
quit;
 
proc print data=work.report_test2;
  title '2) AFTER report_test2 WITH alternate parentheses';
run;

Notice in the results what a difference in results the parentheses make. I would suggest clarifying exactly what rows you want to have returned and then tweak your WHERE clause accordingly.

Cynthia