Desktop productivity for business analysts and programmers

How do I search across all fields for a character/text string?

Reply
Contributor
Posts: 37

How do I search across all fields for a character/text string?

I received a massive SAS data set (around 100 fields and 21 million records) with the comment that records containing the word "TEST" should be removed. The person who told me this didn't know which field(s) might contain the word TEST. Is there a way to search across all fields, without having to individually name the criteria for each field to be checked?  The only thing I can think of would be to create a concatenated field with all other fields and then search that, but even that sounds pretty labor intensive to me. Plus it will take forever and requires creating a large dataset that I don't really need. Can anyone suggest an alternative approach?

 

I am using SAS Enterprise Guide 7.1.

 

Thanks!

 

Super User
Posts: 2,068

Re: How do I search across all fields for a character/text string?

[ Edited ]
Posted in reply to brookeewhite1

data want;
set have;
if index(catx(',',of all_your_fields:  ),"TEST")>0 then delete;
run;

Super User
Posts: 24,026

Re: How do I search across all fields for a character/text string?

Posted in reply to brookeewhite1

 

data have;
set sashelp.class;

flag = find( catx(" ", of _character_), "fred", 'it');
run;

proc print;
run;

 

 

Basically concatenate all the character variables, search for the word TEST in that string. 

This assumes numeric variables cannot be TEST. 

 

You could also run a giant PROC FREQ - get the results in a table and then search that table for the word TEST and see which variables you need to be filtering on.

 

Contributor
Posts: 37

Re: How do I search across all fields for a character/text string?

[ Edited ]

Thank you Reeza, I'm giving this a try now (waiting for it to run)! I'll be out tomorrow so probably will have to come back to this next week. I liked this option because it allows me to view the records containing TEST before I delete them. Also I liked that I didn't have to list all the variables.

Super User
Posts: 24,026

Re: How do I search across all fields for a character/text string?

Posted in reply to brookeewhite1

Note that the it options will strip leading/trailing spaces and ignores case in teh comparison. So in this case, test=Test=TEst etc.

 

 

Contributor
Posts: 37

Re: How do I search across all fields for a character/text string?

Posted in reply to brookeewhite1

Reeza,

 

I tried this solution, but my software keeps crashing, probably due to the amount of data being concatenated, since my data set is so large. Thanks anyway!

Super User
Posts: 2,068

Re: How do I search across all fields for a character/text string?

Posted in reply to brookeewhite1

 

/*or*/

data want;
set have;
array t(*) your_fields: ;
do _n=1 to dim(t);
if index(t(_n),"TEST")>0 then do;
_f=1;
leave;
end;
if _f then delete;
drop _:;
run;

Ask a Question
Discussion stats
  • 6 replies
  • 83 views
  • 0 likes
  • 3 in conversation