10-06-2017 04:35 PM
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....
38 CREATE TABLE WORK.MATERIALS_4 AS
39 SELECT t1. *
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".
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.
10-06-2017 06:22 PM
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.
10-06-2017 11:01 PM
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))
10-09-2017 11:48 AM
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!
10-06-2017 11:56 PM - edited 10-07-2017 12:00 AM
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;
10-09-2017 11:55 AM
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.