topic Re: WHERE Condition not working as expected in SAS Programming

WHERE Condition not working as expected

KiranMaddi — Tue, 22 Nov 2016 11:23:49 GMT

Hi all

I have some test data below and I would like to have columns with both the Test columns not being null/blank. I have tried below and doesn't seem to be working.

Data have;
Name = 'ABC'; 
Test1 = ' ';
Test2 = ' ';output;
Name = 'XYZ'; 
Test1  = ' '; 
Test2 = 'P';output;
run;

Data want;
Set have;
WHERE Test1 NE ' ' and Test2 NE ' ';
RUN;

/* Also tried*/
Data want;
Set have;
WHERE (Test1 NE ' ' and Test2 NE ' ');
RUN;

/* Also tried*/

Data want;
Set have;
WHERE (Test1 NE ' ' )and (Test2 NE ' ');
RUN;

/* Also tried*/

PROC SQL;
Create table want as select * from have where WHERE Test1 NE ' ' and Test2 NE ' ';
QUIT;

But strangely "OR" seems to be working. I am not sure what I am doing wrong here. Please can some one point me to the right direction?

/* Working*/

Data want;
Set have;
WHERE (Test1 NE ' ' or Test2 NE ' ');
RUN;

Re: WHERE Condition not working as expected

Kurt_Bremser — Tue, 22 Nov 2016 11:31:30 GMT

Since you have no observation where your condition is fulfilled, you won't get any output.

Add an observation that meets your criteria:

Data have;
Name = 'ABC'; 
Test1 = ' ';
Test2 = ' ';output;
Name = 'DEF'; 
Test1 = 'x';
Test2 = 'y';output;
Name = 'XYZ'; 
Test1  = ' '; 
Test2 = 'P';output;
run;

Data want;
Set have;
WHERE Test1 NE ' ' and Test2 NE ' ';
RUN;

proc print data=want noobs;
run;

Result:

Name    Test1    Test2

DEF       x        y

Re: WHERE Condition not working as expected

PeterClemmensen — Tue, 22 Nov 2016 11:34:32 GMT

When you use the AND operator, you want both conditions to be true to output, which is not true in any case here, since NE = Not Equals 🙂

Re: WHERE Condition not working as expected

Kurt_Bremser — Tue, 22 Nov 2016 11:48:16 GMT

Now, if your intention was to scan for observations where one of the test variables is not blank, then, per de Morgan's law:

not (A and B) = not A or not B

your OR condition is the one to use.

(A is for "test1 = ' '", B is for "test2 = ' '")

Re: WHERE Condition not working as expected

KiranMaddi — Tue, 22 Nov 2016 12:01:20 GMT

I am confused here. I have observation "XYZ" with value "P" populated. My condition says give all records where tes1 and test2 should not be null, which is true with the observation 'XYZ'?

Re: WHERE Condition not working as expected

KiranMaddi — Tue, 22 Nov 2016 12:03:45 GMT

I guess the condition is true for 'XYZ'.

Re: WHERE Condition not working as expected

PeterClemmensen — Tue, 22 Nov 2016 12:05:12 GMT

No 🙂 It is not true for observation 'XYZ'. Test1 is empty, but Test2 is not. Therefore the condition is not true.

Re: WHERE Condition not working as expected

Kurt_Bremser — Tue, 22 Nov 2016 12:12:11 GMT

@KiranMaddi wrote:
I am confused here. I have observation "XYZ" with value "P" populated. My condition says give all records where tes1 and test2 should not be null, which is true with the observation 'XYZ'?

No. Your condition asks for

(test1 is not null) AND (test2 is not null)

So both have to be populated.

If you wanted for (everyday logic notation)

(test1 or test2) to be not null,

you have to write

test1 ne ' ' or test2 ne ' ';

Re: WHERE Condition not working as expected

mkeintz — Tue, 22 Nov 2016 13:51:14 GMT

Your wording of the problem ("both the Test columns not being null/blank") is a bit ambiguous to me. It could mean that you want each of the TEST columns to be non-blank. But I think you want records in TEST1,TEST2 are not both null.

Is so, then this expression might be more intuitively obvious:

where not(test1=' ' and test2=' ');

or you could use

where cmiss(test1,test2)<2;

The CMISS function counts missing character values.

Re: WHERE Condition not working as expected

KiranMaddi — Tue, 22 Nov 2016 13:20:48 GMT

Thanks mkeintz

This works like a treat. 🙂