Search

[PetePatel]

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

[Astounding · Accepted solution]

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_)

[SuryaKiran]

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;