Help using Base SAS procedures

proc sql like operator filtering missing values

Reply
Frequent Contributor
Posts: 138

proc sql like operator filtering missing values

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.

Valued Guide
Posts: 858

Re: proc sql like operator filtering missing values

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

Super User
Posts: 3,102

Re: proc sql like operator filtering missing values

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

Valued Guide
Posts: 858

Re: proc sql like operator filtering missing values

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

Super User
Posts: 3,102

Re: proc sql like operator filtering missing values

Easy to do! Smiley Happy

PROC Star
Posts: 1,561

Re: proc sql like operator filtering missing values

where  categ_var not like '%B%';

should not filter out missing values.

Super User
Posts: 6,935

Re: proc sql like operator filtering missing values

data want;

set have;

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

run;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Valued Guide
Posts: 765

Re: proc sql like operator filtering missing values

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

Ask a Question
Discussion stats
  • 7 replies
  • 723 views
  • 1 like
  • 6 in conversation