Help using Base SAS procedures

Parenthesis or No Parenthesis

Reply
Contributor
Posts: 59

Parenthesis or No Parenthesis

proc sql;

create table Members as

select ID, L_Name, Join_Date, Member_Type, Area, Member_Code,Partner_Code, Supvr_Code, Refund_Amt, Balance_Owed

  from tbl_Membership

where Member_Type in ('Loss','Active','Contingent') and Member_Code ne '3' or

         Member_Code eq '3' and  Area = '24' or

        Partner_Code eq '1' and Supvr_Code = '5' and

       Balance_Owed >0;

quit;

In my output, I am getting some records with a zero.  I am trying to determine when and where to enclose my or groups and "and' gropus in parenthesis.  I do not deal with this many scenarios often so I want to get a good feel for general rules when dealing with an assortment of and vs or groups.

Super User
Posts: 19,770

Re: Parenthesis or No Parenthesis

Posted in reply to omega1983

Parenthesis are used for specifying the order of how AND/OR are evaluated. Otherwise I think they're evaluated from left to right, which isn't necessarily what you want.

I'll add in the business logic in comments as well so it makes sense to me in the future and for updates.

WHERE

((Member_Type in ('Loss','Active','Contingent') and Member_Code ne '3') /*Member is active (ne 3) and delinquent*/

or (Member_Code eq '3' and  Area = '24') /* OR Member is active and in Los Angeles*/

or  (Partner_Code eq '1' and Supvr_Code = '5') ) /* OR Accounts where the partner is senior and supervisor is not*/

and Balance_Owed >0 /*Any of the above AND Accounts that have a balance outstanding*/

;

Regular Contributor
Posts: 227

Re: Parenthesis or No Parenthesis

Posted in reply to omega1983

Here are some references on

Evaluating Logical Expressions

http://www.sascommunity.org/wiki/Evaluating_Logical_Expressions

This is the support.sas page on order of precedence of evaluation of logical operators

Defining Arithmetic and Logical Expressions

SAS(R) 9.3 Macro Language: Reference

Ron Fehd  logically yours

Ask a Question
Discussion stats
  • 2 replies
  • 157 views
  • 3 likes
  • 3 in conversation