BookmarkSubscribeRSS Feed
cbrotz
Pyrite | Level 9

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!

6 REPLIES 6
ballardw
Super User

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.

cbrotz
Pyrite | Level 9

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

Patrick
Opal | Level 21

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

cbrotz
Pyrite | Level 9

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!

Tom
Super User Tom
Super User

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;

 

cbrotz
Pyrite | Level 9

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 3132 views
  • 3 likes
  • 4 in conversation