topic Re: Where = Data Step option not behaving as expected in SAS Programming

Where = Data Step option not behaving as expected

petergomillion — Tue, 04 Feb 2020 04:30:44 GMT

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.

Re: Where = Data Step option not behaving as expected

mkeintz — Tue, 04 Feb 2020 04:47:45 GMT

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;

Re: Where = Data Step option not behaving as expected

PGStats — Tue, 04 Feb 2020 04:54:07 GMT

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'

Country in ("US", "us")

Re: Where = Data Step option not behaving as expected

Jebastin — Tue, 04 Feb 2020 05:03:36 GMT

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

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

Re: Where = Data Step option not behaving as expected

Satish_Parida — Wed, 05 Feb 2020 08:22:18 GMT

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

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

Re: Where = Data Step option not behaving as expected

Kurt_Bremser — Tue, 04 Feb 2020 06:52:31 GMT

@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.

Re: Where = Data Step option not behaving as expected

Quentin — Tue, 04 Feb 2020 14:27:22 GMT

@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.

Re: Where = Data Step option not behaving as expected

Tom — Tue, 04 Feb 2020 16:56:37 GMT

What about Au or aU?

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

Re: Where = Data Step option not behaving as expected

petergomillion — Tue, 04 Feb 2020 21:45:47 GMT

Those combinations of cases were not in the data set.

Re: Where = Data Step option not behaving as expected

Jebastin — Wed, 05 Feb 2020 08:18:52 GMT

That's correct