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