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!
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.
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
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))
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!
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.