seeing unexpected output

[kajal_30]

Hi All,

I have data set as below and wanted to see where all columns have values 'no' . Tried below code but didn't work.

ID status1 status2 status3 status4

1	yes	no	no	yes
2	no	yes	no	yes
3	yes	yes	no	no
4	yes	no	yes	yes
5	no	no	yes	no

data tesl;
infile datalines ;
input ID status1 $ status2 $ status3 $ status4 $;
datalines ;
1 yes no  no  yes
2 no  yes no  yes
3 yes yes no  no
4 no  no  no  no
5 no  no  yes no
;
run;

proc sql;
select * from tesl 
where status1 not in ('yes') or
status2 not in ('yes') or 
status3 not in ('yes') or 
status4 not in ('yes')
;
quit;
 

output should be :

ID	status1	status2	status3	status4
4	no	no	no	no

Regards

kajal

[PaigeMiller]

If that's what you want, why don't you program it directly like this:

proc sql;
select * from tesl 
where status1 eq 'no' and
status2 eq 'no' and
status3 eq 'no' and
status4 eq 'no' 
;
quit;

or possibly you meant this:

proc sql;
select * from tesl 
where status1 not in ('yes') and
status2 not in ('yes') and 
status3 not in ('yes') and 
status4 not in ('yes')
;
quit;

but the second block of code is logically equivalent to the first block of code (when there are only two possible values 'yes' and 'no'), so save yourself some typing and use the first block of code.

Also, do yourself a favor and represent 'yes' with a numeric 1 and 'no' with a numeric zero. Not only is this less typing, but its fewer opportunities for typographical errors (at least, the way I type) and then the programming is even easier

data want;
    set tesl;
    where sum(of status1-status4)=0;
run;

[Tom]

That is not the result we get from your current code because that is not what your current code is asking for.  You asked for this output:

Notice how every observation selected has NO for at least one of the variables.

If you only want the observations that are NO on every variable (or not YES) then use AND as the conjunction instead of OR.  Note: Make your code easier to read by putting the conjunction string are the start of the line instead of the end.  It is much, much easier for humans to scan the nice smooth left edge of the lines than the jagged right edge of the lines.

proc sql ;
select * from tesl 
where status1 not in ('yes')
  and status2 not in ('yes')
  and status3 not in ('yes')
  and status4 not in ('yes')
;
quit;

[data_null__ · Accepted solution]

Another way assumes all fields are either yes or no.   Probably not realistic scenario. 

data test;
   infile datalines ;
   input ID (status1-status4)($);
   datalines ;
1 yes no  no  yes
2 no  yes no  yes
3 yes yes no  no
4 no  no  no  no
5 no  no  yes no
;
run;

data allno;
   set test;
   allno = not whichc('yes',of status:);
   run;
proc print;
   run;

[Ksharp]

data test;
   infile datalines ;
   input ID (status1-status4)($);
   datalines ;
1 yes no  no  yes
2 no  yes no  yes
3 yes yes no  no
4 no  no  no  no
5 no  no  yes no
;
run;

data allno;
   set test;
if sum(status1='no',status2='no',status3='no',status4='no')=4;
   run;