proc sql like operator filtering missing values

[Walternate]

Hi,

I have a dataset with a categorical variable filled with two-letter values. Some of the values are missing.

ID     Categ_var

1              AD

1             

1              BA

2              BR

2              CB

2             

2              BR

3              AZ

I am trying to filter values of Categ_var that have a B in them, like this:

proc sql;

create table want as

select * from have

where categ_var not like '%B%';

quit;

The problem is that doing this causes SAS to remove the rows in which Categ_var is missing as well, but I want to keep those rows. I tried amending it like this:

proc sql;

create table want as

select * from have

where (categ_var=" " or categ_var not like '%B%');

quit;

But the same thing happened.

Any help is much appreciated.

[Steelers_In_DC]

where not(missing) categ_var and categ_var not like '%B%';

[SASKiwi]

where not missing(categ_var) and categ_var not like '%B%';

[Steelers_In_DC]

Yup, that's what happens when I do two things at once and don't test.

[SASKiwi]

Easy to do!

[ChrisNZ]

where  categ_var not like '%B%';

should not filter out missing values.

[Kurt_Bremser]

data want;

set have;

where indexc(categ_var,'B') = 0;

run;

[MikeZdeb]

Hi.  I agree with Chris@NewZealand in that the LIKE condition is SQL does NOT delete the observations with missing values ...

data test;

input id categ_var :$2. @@;

datalines;

1 AD 1 . 1 BA 2 BR 2 CB 2 . 2 BR 3 AZ

;

title "MISSING VALUES STILL IN DATA SET";

proc sql;title "MISSING VALUES STILL IN DATA SET";

select * from test

where categ_var not like '%B%';

quit;

MISSING VALUES STILL IN DATA SET

      id  categ_var

       1  AD

       1

       2

       3  AZ