topic Re: Question on Where statement in Proc step in SAS Programming

Question on Where statement in Proc step

Amali6 — Fri, 15 May 2020 00:35:13 GMT

Hi,

Can someone say how many conditions are allowed to be given inside a where statement in the Proc step please?

Thanks in advance

Re: Question on Where statement in Proc step

Tom — Fri, 15 May 2020 01:22:29 GMT

I doubt there is a design limit. There might be some physical limit it terms of memory usage or compile time.

Re: Question on Where statement in Proc step

ballardw — Fri, 15 May 2020 06:39:56 GMT

@Amali6 wrote:

Hi,

Can someone say how many conditions are allowed to be given inside a where statement in the Proc step please?

Thanks in advance

I doubt that such a question arises without having a specific use issue. You might show the specific where that you attempted and discuss any issues.

A complex where clause will take you into the realm of truth tables and how to result the behavior of AND and OR bits of a statement.

There is a limit of one WHERE statement per proc or data step.

Re: Question on Where statement in Proc step

s_lassen — Fri, 15 May 2020 10:10:40 GMT

Only one. E.g.

proc print data=sashelp.class;
  where sex='F' and age>14;
run;

has only one condition, which is compound.

You can use WHERE ALSO to expand on the initial condition, but that is just the same as using AND.

The code

proc print data=sashelp.class;
  where sex='F';
  where also age>14;
run;

gives the same result as the first example.

There are various limits to the length of conditions on different platforms.

For instance, some DBMS systems (possibly also SAS, I haven't checked) have a limit on the number of values you can put in an IN(...) condition. In the good old days a lot of work was done to get around this limitation in SAS/ACCESS to DB2, I think the limit was 1000 values at that time, and that was not always enough (there was no DBKEY data set option in SAS/ACCESS at that time, which would be the obvious solution today). Last time I hit this limit was on Oracle, I think it was 10000 values. But again, that may have changed now.

Re: Question on Where statement in Proc step

Amali6 — Fri, 15 May 2020 23:43:08 GMT

Thank you. But can i ask you, whether sas accepts more than three conditions in the where statement?

Re: Question on Where statement in Proc step

ChrisNZ — Sat, 16 May 2020 02:21:19 GMT

> can i ask you, whether sas accepts more than three conditions in the where statement?

Yes, there is not limit as was said above.

Can I ask you why you don't try rather than ask here? It would be much faster.