Where = Data Step option not behaving as expected

[petergomillion]

Hi! 

So I have an exercise where (no pun intended) I need to keep the observations for the variable Country that is equal to "US" or 'us'. 

I'm tired but I don't see why this code is not working. 

proc sort data= "/folders/myfolders/cert/input/input27.sas7bdat" out=results_output27a
(where=(Country ne 'AU' OR 'au'));
by state descending Postal_code; 
run; 

I tried this code above with single quotes and double-quotes. When I run this code what do I see...AU in my results! I was fuming. 

Eventually, I found a workaround by choosing a data set statement where. Where the state is missing. That worked but I want to know why the above code did not work. According to the documentation, it should work.

[mkeintz · Accepted solution]

The expression

    WHERE=(country ne 'AU' or 'au');

is parsed as

   WHERE=( (country ne 'AU')  or 'au');

The expression after the 'or'   is non-zero (non-blank in this case), and therefore interpreted as true.  So you should have in your log (ALWAYS look at your log) the note

WHERE 1 /* an obviously TRUE WHERE clause */ ;

As an example please run:

data _null_;
  set sashelp.class (where=(sex ne 'F' or 'M'));
run;

Consider doing this:

proc sort data= "/folders/myfolders/cert/input/input27.sas7bdat" out=results_output27a
(where=(Country not in ('AU','au')));
by state descending Postal_code; 
run; 

[Kurt_Bremser]

---

@mkeintz wrote:

ALWAYS look at your log

---

To @petergomillion: Also known as Maxim 2 (see link "Maxims of Maximally Efficient SAS Programmers"). There is a VERY BIG REASON why this maxim is ranked so high up on the list.

[PGStats]

According to the rules of operator precedence, expression

Country ne 'AU' OR 'au'

is evaluated as

(Country ne 'AU') OR 'au'

The expression in parenthesis returns either 0 or 1, which SAS tries to combine with "au", which, since it isn't missing ("  ") is considered true (1), hence the result is always true.

You probably want:

Country ne 'AU' AND Country ne 'au'

or

Country in ("US", "us")

[Jebastin]

Have your tried giving the variable name for each conditions like below,

where=(country ^= 'AU' OR country ^= 'au');

[Quentin]

---

@Jebastin wrote:

Have your tried giving the variable name for each conditions like below,

where=(country ^= 'AU' OR country ^= 'au');

---

With an OR, that would always be true.  You could do that with an AND.

[Jebastin]

That's correct 

[Satish_Parida]

where=(strip(lowcase(country)) = 'au')

Better use a libname to access the dataset for clean code.

[Tom]

What about Au or aU?

where=( upcase(country) ne 'AU' )

[petergomillion]

Those combinations of cases were not in the data set.