topic Re: Using Contains Statement with a Macro in Proc SQL in SAS Programming

Using Contains Statement with a Macro in Proc SQL

cbrotz — Fri, 06 Oct 2017 20:35:34 GMT

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!

Re: Using Contains Statement with a Macro in Proc SQL

ballardw — Fri, 06 Oct 2017 22:22:41 GMT

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.

Re: Using Contains Statement with a Macro in Proc SQL

cbrotz — Fri, 06 Oct 2017 23:20:20 GMT

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

Re: Using Contains Statement with a Macro in Proc SQL

Patrick — Sat, 07 Oct 2017 03:01:05 GMT

@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))

Re: Using Contains Statement with a Macro in Proc SQL

Tom — Sat, 07 Oct 2017 04:00:22 GMT

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;

Re: Using Contains Statement with a Macro in Proc SQL

cbrotz — Mon, 09 Oct 2017 15:48:43 GMT

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!

Re: Using Contains Statement with a Macro in Proc SQL

cbrotz — Mon, 09 Oct 2017 15:55:44 GMT

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.