SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Removing observations if case does not contain certain words

Accepted Solution Solved
Reply
Contributor
Posts: 50
Accepted Solution

Removing observations if case does not contain certain words

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,


Accepted Solutions
Solution
‎10-24-2016 07:27 PM
Super User
Posts: 5,088

Re: Removing observations if case does not contain certain words

[ Edited ]

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.

View solution in original post


All Replies
Solution
‎10-24-2016 07:27 PM
Super User
Posts: 5,088

Re: Removing observations if case does not contain certain words

[ Edited ]

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.

PROC Star
Posts: 1,095

Re: Removing observations if case does not contain certain words

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

 

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

 

Tom

Respected Advisor
Posts: 4,654

Re: Removing observations if case does not contain certain words

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

PG
Contributor
Posts: 50

Re: Removing observations if case does not contain certain words

Thank you.
Super User
Posts: 9,687

Re: Removing observations if case does not contain certain words

Can you post your data and output here ?

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 714 views
  • 4 likes
  • 5 in conversation