Filtering with a Parameter - Syntax Error

Reply
Contributor
Posts: 51

Filtering with a Parameter - Syntax Error

I have a parameter that I'm using to filter a table.  The parameter collects a series of values, all strings, and the filter is designed to look at a single column to see if any of values appear in any part of each row. The columns contain addresses, the parameter is counties, so you can select which counties to include in the analysis.  I've used CONTAINS in the filter so I can pick out some non-standard addresses and adapt my computed columns to do a better job of picking up the addresses I'm interested in.  Using Enterprise Guide 4 for this.

 

The filter wizard produces the following filter:

 

Table.Column CONTAINS '&Param'

 

I've also tried:

 

Table.Column CONTAINS (&Param)

 

Neither work, both have different issues.  I have ticked to enclose the values of the parameter in quotes, which I need to do to get a different part of the query to work.

 

The parameter is returning "County1" , "County2", "County3".......etc

 

There'll be sometihng obvious wrong, but I've no idea what it is or what the solution is, so would really appreciate a few pointers!

 

Thanks

Super User
Posts: 7,433

Re: Filtering with a Parameter - Syntax Error

You can't use a list of strings to search for with contains, you can only search for one string.

When you have a list of strings to search for, you could create the whole condition in a previous step and store it to a macro variable:

%let list=country1 country2 country3;

data _null_;
length wherecond $ 1000;
i = countw("&list");
put i=;
do i = 1 to countw("&list");
  wherecond = catx(' and ',trim(wherecond),'column contains "'!!trim(scan("&list",i))!!'"');
end;
call symput('wherecond',trim(wherecond));
run;
%put &wherecond;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,720

Re: Filtering with a Parameter - Syntax Error

Query?  What software is this your using, one of the UI versions I suppose.  The contains has to have data such as:

contains '{%}<string>{%}'

The {%} is optional depending on scenario.  So you can't have a list of values going into it.  Why do you need contains?  Would not:

in (&param.)

Not work, then would output all records where country is within the list.  Otherwise if the country has other data in it, then you going to have to do some funky perl or something similar.

 

Contributor
Posts: 51

Re: Filtering with a Parameter - Syntax Error

Sorry, maybe I do need IN, I'm quite new to this. 

 

If IN returns rows where any of the parameter result is anywhere in the column then that sounds like the answer I'm after.  I'll have a look and see....

 

Thanks!

Super User
Super User
Posts: 7,720

Re: Filtering with a Parameter - Syntax Error

Nope, in only checks the whole of the string against the whle of the list value.  As I said, if it is just parts of strings you are looking for then that is going to be a lot more complicated.  You would need to somehow loop over each element in your list then find that element in the string.  Again, I don't know what software you are using but in Base SAS;

data want;
  set have;
  do i=1 to countw("&param.",",");
    if index(thestring,scan("&param.",i,",")) then output;
  end;
run;
Contributor
Posts: 51

Re: Filtering with a Parameter - Syntax Error

OK, thanks, that's great I can work with that.

Ask a Question
Discussion stats
  • 5 replies
  • 329 views
  • 2 likes
  • 3 in conversation