07-05-2018 04:09 PM
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.
07-05-2018 04:15 PM - edited 07-05-2018 04:15 PM
if index(catx(',',of all_your_fields: ),"TEST")>0 then delete;
07-05-2018 04:18 PM
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.
07-05-2018 04:52 PM - edited 07-05-2018 04:53 PM
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.
07-05-2018 04:54 PM
Note that the it options will strip leading/trailing spaces and ignores case in teh comparison. So in this case, test=Test=TEst etc.
07-09-2018 12:29 PM
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!
07-05-2018 04:21 PM
array t(*) your_fields: ;
do _n=1 to dim(t);
if index(t(_n),"TEST")>0 then do;
if _f then delete;