DATA Step, Macro, Functions and more

WHERE Condition not working as expected

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 107
Accepted Solution

WHERE Condition not working as expected

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;

Accepted Solutions
Solution
‎11-22-2016 08:19 AM
Trusted Advisor
Posts: 1,019

Re: WHERE Condition not working as expected

[ Edited ]
Posted in reply to KiranMaddi

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.

View solution in original post


All Replies
Super User
Posts: 7,782

Re: WHERE Condition not working as expected

Posted in reply to KiranMaddi

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  
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 107

Re: WHERE Condition not working as expected

Posted in reply to KurtBremser
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'?
PROC Star
Posts: 746

Re: WHERE Condition not working as expected

Posted in reply to KiranMaddi

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

Super User
Posts: 7,782

Re: WHERE Condition not working as expected

Posted in reply to KiranMaddi

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 ' ';

!!

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 746

Re: WHERE Condition not working as expected

Posted in reply to KiranMaddi

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 Smiley Happy

Frequent Contributor
Posts: 107

Re: WHERE Condition not working as expected

I guess the condition is true for 'XYZ'.
Super User
Posts: 7,782

Re: WHERE Condition not working as expected

Posted in reply to KiranMaddi

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 = ' '")

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎11-22-2016 08:19 AM
Trusted Advisor
Posts: 1,019

Re: WHERE Condition not working as expected

[ Edited ]
Posted in reply to KiranMaddi

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.

Frequent Contributor
Posts: 107

Re: WHERE Condition not working as expected

Thanks mkeintz

This works like a treat. Smiley Happy

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 253 views
  • 0 likes
  • 4 in conversation