Select observations that do NOT meet a criteria

[apple]

HH_NO	X
01	9
01	3
02	9
03	1
03	2

I have a similar dataset but with more rows. I want to select all the HH_NO that do NOT have any X=9, like HH_NO=03

How do I do so?

Thank you

[LinusH]

Many ways to this. SQL with a subquery (inner join or where) are two.

[kuridisanjeev]

As Linush said,there are many ways to do this,

Here is one of the way,

Data test;

input a b ;

cards;

01 9

01 3

02 9

03 1

03 2

run;

Proc Sql;

select Distinct a into :value separated by ' '  from test

where b eq 9;

quit;

Data final;

set test;

if a not in (&value);

run;

Proc print;

run;

[kuridisanjeev]

One more way,

Data test;

input a b ;

cards;

01 9

01 3

02 9

03 1

03 2

run;

Proc Sql;

select A,b from test

where a not in (Select distinct a from test where b eq 9);

quit;

Thanks,

Sanjeev.K