topic Re: proc sql like operator filtering missing values in SAS Procedures

proc sql like operator filtering missing values

Walternate — Wed, 29 Jul 2015 18:42:32 GMT

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 BA

2 BR

2 CB

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.

Re: proc sql like operator filtering missing values

Steelers_In_DC — Wed, 29 Jul 2015 19:00:43 GMT

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

Re: proc sql like operator filtering missing values

SASKiwi — Wed, 29 Jul 2015 19:30:01 GMT

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

Re: proc sql like operator filtering missing values

Steelers_In_DC — Wed, 29 Jul 2015 19:34:42 GMT

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

Re: proc sql like operator filtering missing values

SASKiwi — Wed, 29 Jul 2015 22:25:52 GMT

Easy to do!

Re: proc sql like operator filtering missing values

ChrisNZ — Thu, 30 Jul 2015 04:02:27 GMT

where categ_var not like '%B%';

should not filter out missing values.

Re: proc sql like operator filtering missing values

Kurt_Bremser — Thu, 30 Jul 2015 06:50:29 GMT

data want;

set have;

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

run;

Re: proc sql like operator filtering missing values

MikeZdeb — Thu, 30 Jul 2015 18:26:32 GMT

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

3 AZ