BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
r4321
Pyrite | Level 9

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,

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

5 REPLIES 5
Astounding
PROC Star

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.

PGStats
Opal | Level 21

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

PG
Ksharp
Super User

Can you post your data and output here ?

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!

How to connect to databases in SAS Viya

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.

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