Select observations that do NOT meet a criteria

Reply
Frequent Contributor
Posts: 75

Select observations that do NOT meet a criteria

HH_NOX
019
013
029
031
032

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

Super User
Posts: 5,260

Re: Select observations that do NOT meet a criteria

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

Data never sleeps
Super Contributor
Posts: 276

Re: Select observations that do NOT meet a criteria

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;

Super Contributor
Posts: 276

Re: Select observations that do NOT meet a criteria

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

Ask a Question
Discussion stats
  • 3 replies
  • 223 views
  • 0 likes
  • 3 in conversation