topic Re: Removing observations if case does not contain certain words in SAS Data Management

Removing observations if case does not contain certain words

r4321 — Wed, 19 Oct 2016 17:42:55 GMT

Hello everyone,

I have a large dataset, many thousands of observations. However, only certain observations are relevant to me. If the data were not so messy, I would consider another way to approach what I want to do, but alas...

I want to delete cases that do not contain certain keywords on any of the variables. For example, the dataset contains events that have happened at public institutions. And I only want to keep the observations that mention "college" or "University" or "school" or "academy." The tricky thing is that I don't know where in the text strings of the certain variables that may reference these entities will show up. Sometimes the keyword "school" could show up on variable 5, sometimes the keyword "university" could show up on variable 3 and vice versa. Furthermore, these words are not the only words that will be in the text string of a variable. It will typically say something like ... happened at Y school. I only want to keep these sorts of observations and I want to delete others that mention other public institutions, e.g., state offices, etc.

Hopefully I've explained this adequately, your guidance is very much appreciated.

Best,

Re: Removing observations if case does not contain certain words

Astounding — Wed, 19 Oct 2016 18:09:23 GMT

As long as you don't have a zillion variables, this should do it:

data want;

set have;

length test $ 32000;

test = upcase(cats(of _character_));

if index(test, 'UNIVERSITY')

or index(test, 'SCHOOL')

or index(test, 'COLLEGE')

or index(test, 'ACADEMY');

drop test;

run;

The tricky part is that a length of 32,000 has to be long enough to hold all of your character variables put together.

Re: Removing observations if case does not contain certain words

TomKari — Wed, 19 Oct 2016 19:22:16 GMT

Here's a similar case, and some different approaches:

http://support.sas.com/resources/papers/proceedings14/1717-2014.pdf

Tom

Re: Removing observations if case does not contain certain words

PGStats — Wed, 19 Oct 2016 20:08:08 GMT

Great idea. However, use catx(" ", of _character_) instead of cats(), and indexw() instead of index() to detect words.

Re: Removing observations if case does not contain certain words

Ksharp — Thu, 20 Oct 2016 10:05:48 GMT

Can you post your data and output here ?

Re: Removing observations if case does not contain certain words

r4321 — Mon, 24 Oct 2016 23:27:49 GMT

Thank you.