BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
WGE914
Calcite | Level 5

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.  

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

 

 

View solution in original post

6 REPLIES 6
PaigeMiller
Diamond | Level 26

@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;
           
--
Paige Miller
WGE914
Calcite | Level 5

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

PaigeMiller
Diamond | Level 26

flag=find(comments,code);

 

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

--
Paige Miller
ballardw
Super User

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.

 

 

WGE914
Calcite | Level 5

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.

 

 

 

PGStats
Opal | Level 21

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.

 

PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 5595 views
  • 1 like
  • 4 in conversation