SAS SQL stream - Multiple 'and' statements & multiple 'OR' statements

[KRHE]

I'm running a code, where the 'and' functions are in line, however sas does not acknowledge my 'or' statements. Also, my "salery" variable is a string. Please help, have battled this too long. 😛 Nb: if i change the first salery statements to "and" then the other "or" -statements do not work.

FROM server as Server1

where (age between '16' and '35')
and (IN_SEX = 'M')
or (salery between '16700' and '16780')  
 or (salery between '17120' and '17160') 
 or (salery between '18720' and '18780') 

);
quit; ```

[PaigeMiller]

You haven't really stated what output is desired, and of course its hard to state that in words. Perhaps you could provide a small example data set and then show us exactly what output you want.

However, there is an order of evaluation in SAS where AND is evaluated before OR — and maybe you want OR to be evaluated before AND?? I don't know if that's what you want, because you haven't told us clearly what you did want.

Nevertheless, you adjust the order of evaluation by using parentheses. So these two PROC SQLs are not going to give the same results (notice the difference in parentheses).

proc sql;
create table abc as select * from sashelp.cars
where (enginesize between 2 and 4)
and (origin = 'Asia')
or (invoice between 26000 and 26999)
 or (invoice between 28800 and 28999)
 or (invoice between 30000 and 30999);
quit;

proc sql;
create table abc as select * from sashelp.cars
where (enginesize between 2 and 4)
and ((origin = 'Asia')
or (invoice between 26000 and 26999)  
 or (invoice between 28800 and 28999) 
 or (invoice between 30000 and 30999));
quit; 

[Reeza]

FROM server as Server1

where (age between '16' and '35')
and (IN_SEX = 'M')
AND ( (salery between '16700' and '16780')  
 or (salery between '17120' and '17160') 
 or (salery between '18720' and '18780') )

);
quit; 

Or

FROM server as Server1

where (age between '16' and '35')
and (IN_SEX = 'M')
and ((input(salery, 8.) between 16700 and 16780)  
 or (input(salery, 8.) between 17120 and 17160) 
 or (input(salery, 8.) between 18720 and 18780) 

);
quit; 

---

@KRHE wrote:

I'm running a code, where the 'and' functions are in line, however sas does not acknowledge my 'or' statements. Also, my "salery" variable is a string. Please help, have battled this too long. 😛 Nb: if i change the first salery statements to "and" then the other "or" -statements do not work.

FROM server as Server1

where (age between '16' and '35')
and (IN_SEX = 'M')
or (salery between '16700' and '16780')  
 or (salery between '17120' and '17160') 
 or (salery between '18720' and '18780') 

);
quit; ```

---

[Kurt_Bremser]

AND is evaluated before OR. This means that your age and sex become irrelevant if any of the salary conditions is met. You will get all males between 16 and 35, combined with all persons that meet one of the salary ranges.

BTW who came up with the crazy idea of storing incomes and ages as character? Note that a salary of '1675' or an age of '2'(!) would fall in your ranges.

[ballardw]

"Between" and character values, i.e. '16' , '35' seldom return as expected.

SAS will not convert the characters to numeric values and comparisons with character values are done character by character until characters are exhausted. Which means that '2' will be reported as between '16' and '35' as '2' is larger than '1' and less than '3'.

For similar reasons a value such as '167030' would be between '16700' and '16780'.

What is the specific reason you are apparently excluding values between 16780 and 17120? 

If your values of Age and Salery(sic) are actually character you probably should change them to numeric as a first step.

Research DeMorgans Laws for what happens when evaluating combinations of AND and OR logical results.

[KRHE · Accepted solution]

Hi everyone, Seems I solved it!

What I wanted is the following: Customer HAD to be of the age between 16 and 35, the customer also HAD to be male. The salary COULD ONLY range between THREE different intervals.

FROM server as Server1

where (age between '16' and '35')
and (IN_SEX = 'M')
and (salery between '16700' and '16780'  
 or salery between '17120' and '17160' 
 or salery between '18720' and '18780') 

);
quit; ```

 Best regards, and thanks to all of you!

One small issue is that i have some salaries that are one number smaller but i will try to use the  length  function to returns the length of the string char-