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

[brookeewhite1]

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!

[novinosrin]

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

[Reeza]

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.

[brookeewhite1]

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.

[Reeza]

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

[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!

[DrAbhijeetSafai]

That was helpful! 🙂

Thank you.

- Dr. Abhijeet Safai

[novinosrin]

/*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;