BookmarkSubscribeRSS Feed
Walternate
Obsidian | Level 7

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.

7 REPLIES 7
Steelers_In_DC
Barite | Level 11

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

SASKiwi
PROC Star

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

Steelers_In_DC
Barite | Level 11

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

ChrisNZ
Tourmaline | Level 20

where  categ_var not like '%B%';

should not filter out missing values.

MikeZdeb
Rhodochrosite | Level 12

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 4137 views
  • 1 like
  • 6 in conversation