DATA Step, Macro, Functions and more

Search

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

Search

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


Accepted Solutions
Solution
4 weeks ago
Super User
Posts: 6,541

Re: Search

Posted in reply to PetePatel

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

 

 

View solution in original post


All Replies
Solution
4 weeks ago
Super User
Posts: 6,541

Re: Search

Posted in reply to PetePatel

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

 

 

Super Contributor
Posts: 469

Re: Search

Posted in reply to PetePatel

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;
Thanks,
Suryakiran
☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 84 views
  • 1 like
  • 3 in conversation