Using Contains Statement with a Macro in Proc SQL

[cbrotz]

Is it possible to use the Contains statement together with a macro list with more than one lookup?  I am trying do multiple lookups using partial strings.   It works if just one selection is made but not more than one....If I just send through "ASSORTED" for example, I get all Mat_Desc that contain the word "Assorted".  I would like to do a lookup on all that are i the list....I tried the LIKE statement and was not having much luck with that either....

     PROC SQL;
38            CREATE TABLE WORK.MATERIALS_4 AS
39            SELECT t1. *
40         
41               FROM BIRD.V_MATERIALS t1
42               where t1.MAT_DESC CONTAINS (&value_list6);
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Line generated by the macro variable "VALUE_LIST6".
42         "ASSORTED","POSTERS"
                     _
                     22
                     76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
              CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.  

ERROR 76-322: Syntax error, statement will be ignored.

Thank you!

[ballardw]

Contains want to compare a single value

you would want something like

where  t1.MAT_DESC CONTAINS (&value1) 
   or  t1.MAT_DESC CONTAINS (&value2) 
   or  t1.MAT_DESC CONTAINS (&value3)
; 
 

Did you try hardcoding an example of this with two values and no macro variable before attempting this code?

If this is inside an actual macro block defined with %macro  ... %mend statements you could count the number of elements in &value list and build the "or" statements for each value.

If Mat_desc is supposed to actually be "ASSORTED" and doesn't have values like "ASSORTED HARDWARE" you could us the IN comparison.

[cbrotz]

Yes this code does work when hard coded. I might have to just limit them to 3 entries or something similar to what you have.  Thanks

[Patrick]

@cbrotz

Just a thought in case you're passing in the values via a prompt: It's sometimes necessary to %unquote() the string for downstream processing with code as below:

....where t1.MAT_DESC CONTAINS (%unquote(&value_list6))

[cbrotz]

That is really good to know.  I already ran into this problem and ended up writing a SAS step to fix. Thanks for next time -  it will save me coding!

[Tom]

Can you explain a little better what you are trying to do?

The CONTAIN operator does NOT support multiple values at once. You can NOT write a statement like this

name contains ('a','b')

If you want to pass a list of values into a SAS macro then you could use macro code to loop over the items in that list and generate a series of CONTAIN clauses connected by OR. So something like this:

name contains 'a' or name contains 'b'

For example you might create a macro like this:

%macro contains(var,values);
%local i sep ;
%do i=1 %to %sysfunc(countw(&values,%str(,),mq));
&sep &var contains %scan(&values,&i,%str(,),mq)
%let sep=or;
%end;
%mend contains;

So then you could use it like this.

proc sql ;
 select * from sashelp.class
 where %contains(name,%str('a','b'))
 ;
quit;

[cbrotz]

Hi Tom,  Thanks again for your help....yes after they started using the prompts, they asked if they could enter parts of descriptions.....The list of material descriptions is too long and I cannot create a list for them to pick from as I did with other data  - SAS won't do a lookup past 5000 or so?  That makes sense.  I can't imagine it is helpful to have to scroll through thousands of entries....  Anyway, I am limiting them to 10 descriptions or partial descriptions and coded for 10.....we will see how that goes.