BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
KiranMaddi
Obsidian | Level 7

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;
1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

9 REPLIES 9
Kurt_Bremser
Super User

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  
KiranMaddi
Obsidian | Level 7
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'?
PeterClemmensen
Tourmaline | Level 20

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

Kurt_Bremser
Super User

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

!!

PeterClemmensen
Tourmaline | Level 20

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 🙂

KiranMaddi
Obsidian | Level 7
I guess the condition is true for 'XYZ'.
Kurt_Bremser
Super User

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

mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
KiranMaddi
Obsidian | Level 7
Thanks mkeintz

This works like a treat. 🙂

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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