2 weeks ago
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.
2 weeks ago
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.
2 weeks ago
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))
2 weeks ago - last edited 2 weeks ago
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;
a week ago
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.