topic Re: Text search based on Column values in SAS Procedures

Text search based on Column values

WGE914 — Tue, 17 Nov 2015 15:46:15 GMT

I have a text field ("Comments") in survey data. I need to search that field for values in a column in another table. I know how to do a basic search based on a single specified value or discrete SET of values I can easily type out individually. However, there are too many values to type for what I want to search. I need to search the comments field for all responses, where it contains one of the codes contained in a column. Any help would be greatly appreciated.

Re: Text search based on Column values

PaigeMiller — Tue, 17 Nov 2015 15:54:26 GMT

@WGE914 wrote:

I have a text field ("Comments") in survey data. I need to search that field for values in a column in another table.

If the values you want to search for are in another table, first you'd have to combine the table with Comments and the table with the values to search for.

I know how to do a basic search based on a single specified value or discrete SET of values I can easily type out individually. However, there are too many values to type for what I want to search. I need to search the comments field for all responses, where it contains one of the codes contained in a column. Any help would be greatly appreciated.

So multiple columns of values to search for. Sounds like you need an array, and then loop over all of the columns in your search. If the value of count is greater than 0, then you have found that the comment field contains at least one of these values. In this example, I have assumed there are 100 variables with the values to search for, named col1-col100, but naturally you can replace that part of the code with the exact column names.

UNTESTED CODE

data doing_the_search;
     set combined;
     array col column1-column100;
     count=0;
     do i=1 to 100;
           if find(comments,col{i})>0 then count=count+1;
     end;
run;

Re: Text search based on Column values

WGE914 — Tue, 17 Nov 2015 16:00:04 GMT

Either I'm reading your response incorrectly, or there's a misunderstanding. There is only one column which has the values I want to search for. Call this column "Code". Each respondent filled it in, and most of them have text in the comments field. I want to search the "Comments" field for value in the "Code" column. The Code column is text, but everything is the same length (5).

Re: Text search based on Column values

ballardw — Tue, 17 Nov 2015 16:00:33 GMT

Do you have the terms you want to search for in some form that you can read into a SAS data set? That would be your best bet.

Then probably one or more SQL merges with that search item dataset and your surveydata would work.

Something like

proc sql;

create table found as

select a.searchterm, b.comment

from searchtermdataset as a join surveydataset as b

where upcase(b.comment) contains upcase(a.searchterm);

quit;

Note: this will only find exact matches, may miss similar terms like singular if searching for plural.

Re: Text search based on Column values

PaigeMiller — Tue, 17 Nov 2015 16:43:13 GMT

flag=find(comments,code);

If flag>0 then you have found the text string in code within the text string called comments.

Re: Text search based on Column values

WGE914 — Tue, 17 Nov 2015 17:34:13 GMT

I don't think I was clear, Iapologize. I want to search ALL values in the "code" column, not just for the value in the corresponding row. So, it would require some sort of loop, I know that much.

In other words, I need something that would work the same way as:

select * from survey_data

where comments like "%(select code from survey_data)%"

I've tried a variation of the above statement using a macro variable within the like statement, but it only reads the first row. I need a macro to be read as an array.

Re: Text search based on Column values

PGStats — Tue, 17 Nov 2015 18:10:26 GMT

Follow @ballardw's advice, join the two tables with a CONTAINS operation :

proc sql;
   create table found as
   select a.searchterm, b.comment
   from searchtermdataset as a cross join surveydataset as b
   where upcase(b.comment) contains upcase(a.searchterm);
quit;

If that doesn't work, please tell us why. Stay away from macro programming, it's inefficient and not required here.