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;
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.
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
No 🙂 It is not true for observation 'XYZ'. Test1 is empty, but Test2 is not. Therefore the condition is not true.
@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 ' ';
!!
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 🙂
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 = ' '")
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.