Hi all,
I have a large SAS dataset with >100k records and it contains variables such as:
forename, surname, company, email address...etc.
I would like to search for a particular word (example 'TSE') in ANY of the variables and keep these records only.
How do I go about writing this? I tried some code like the below, but it only returns records for one variable (i.e surname).
data want;
set have (where=(prxmatch("/\b(TSE)\b/io", surname)));
run;
Any help would be greatly appreciated.
Cheers,
Pete
If you knew ahead of time that there was a short list of character variables, you could replace SURNAME with an expression that smooshes them all together, such as:
catx(' ', surname, forename, company, email)
With longer lists of variable names, or with lists that change from data set to data set, you would need to jump through some massive hoops using preprocessing of the data to find the names of all the character variables,. It would be much easier to switch from WHERE to IF, which would allow the use of the word _CHARACTER_ to represent all character variables. If you do switch to an IF statement you should be able to replace SURNAME with:
catx(' ', of _character_)
If you knew ahead of time that there was a short list of character variables, you could replace SURNAME with an expression that smooshes them all together, such as:
catx(' ', surname, forename, company, email)
With longer lists of variable names, or with lists that change from data set to data set, you would need to jump through some massive hoops using preprocessing of the data to find the names of all the character variables,. It would be much easier to switch from WHERE to IF, which would allow the use of the word _CHARACTER_ to represent all character variables. If you do switch to an IF statement you should be able to replace SURNAME with:
catx(' ', of _character_)
There are many function in SAS to find a string, I prefer INDEX or FIND if I'm searching for a single string and PRXMATCH for multiple strings.
First you need to find all the character variables in your table and then find the string in each variable.
proc sql;
select "FIND(strip(upcase("||name||")),'AUDI')<>0" INTO: Check_Vars separated by " OR "
from dictionary.columns
where libname="SASHELP" and MEMNAME="CARS"
and type='char';
quit;
data want;
set sashelp.cars(where=(&Check_Vars.));
run;
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.