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,
R
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.
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.
Here's a similar case, and some different approaches:
http://support.sas.com/resources/papers/proceedings14/1717-2014.pdf
Tom
Great idea. However, use catx(" ", of _character_) instead of cats(), and indexw() instead of index() to detect words.
Can you post your data and output here ?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.