SAS question about query

[madeline1]

I have been using SAS now for only about a week. My question is how do I create a where clause that will allow me to do a contains on more than one variable character in my dataset. That my not be clear, but I want to filter on more than one item in my dataset.

Thanks!

 

[ChrisW75]

Madeline
Hi, and welcome to SAS.
The syntax for multiple where clauses is pretty simple to remember, you just list all the clauses one after another. I think it's one of the shortfalls in the doco that they generally don't show examples of things like this.

data outpt;
  set inpt(where=(A=1 and B=2));
run;

For more complex filters, I tend to put them into the step, rather than in a dataset option...

data outpt;
  set input;

  if upcase(compress(A_text)) NE 'DAVE' then delete;
run;

You may want to experiment a little with which works best. The "Where=" dataset option is usually more efficient as it is processed before the full record is read in.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[madeline1]

I am using SAS Enterprise does that matter. I was trying to use the filter feature?

 

[ChrisW75]

OK. So, what exactly are you trying to filter? You should be able to create multiple filters, even on the same variable and then and/or them together to build the filter you need. Just keep dragging in variables and building up new filters one at a time.

If you're trying to build up something complex that you simply can't get to work that way, it may be easier create a code node and manually type out the code.

If you post here exactly what you're trying to do, I can show you how.


Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[madeline1]

I have selected the column I want to filter, but the filter section only allows to select one value from the list. Here is an example of what I am trying to do
Cities.City = (''New York' 'Brooklyn'') I want to filter both cities so only those results will display.

Thanks

 

[klaz2002]

in regular code (I guess with enterprise this would be in a code node) you could do this severla ways.

ex

where trim(upcase(city)) in ('NEW YORK','BROOKLYN');

ex2

where trim(upcase(city)) = 'NEW YORK' or
      trim(upcase(city)) = 'BROOKLYN';

Does this help you?

 

[ChrisW75]

In the filter, there should be an "in a list of values" option, which builds up the IN statement as shown in Klaz's response.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.