DATA Step, Macro, Functions and more

multiple or conditions in where clause

Reply
Occasional Contributor mj5
Occasional Contributor
Posts: 18

multiple or conditions in where clause

I have a program in which in proc sql i need to use multiple where conditions  that is 

where 20 gt win1 lt 30 or 20 gt win2 lt 30 or 20 gt win3 lt 30................till win20.

 

Is there any way not to use multiple or conditions?

Super User
Posts: 9,549

Re: multiple or conditions in where clause

No. You'll have to code all separate conditions. But you can automate that coding with macro preprocessing.

BTW,

20 gt win1 lt 30

is equivalent to

win1 lt 20

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Trusted Advisor
Posts: 1,822

Re: multiple or conditions in where clause

Posted in reply to KurtBremser

There are few ways to do it, but assuming the win should be in a range of values.

(See @KurtBremser note):

 

1)

%macro chk;
       %do i=1 to 19;
           IF
          %str(  <limit1> le win&i le <limit2> or);
       %end;
          %str( <limit1> le win20 le <limit2>; );
    %mend;
     data want; /* or sql step */
        set have;
             %chk;
     run;

2) Use datasetep instead sql:

  

data want;
    set have;
      array winx win1 - win20;
      result = 0;
      do i=1 to 20;
           result = result or (<limit1> le winx(i) le <limit2>);
           if result = 1 then leave;  /* enough to find the first */
      end;
      if result = 1;
     drop result;
run;

        

Super User
Super User
Posts: 9,193

Re: multiple or conditions in where clause

There are always several ways to approach a problem.  Your main issue here is that your data is not modelled in a way that is easily programmable in SQL.  SQL was developed for relational databases, so this means lots of tables with unique data and id's linking them.  These tables were normalised - data going down the page rather than across.  Unfortunately nowadays a lot of "Excel thinking" has crept in and people are trying to code against transposed (goes across the page) data.  From what you post, if your data looked like this:

WIN_NO   RESULT

1                12

2                30

Your where clause would resolve to:

where RESULT between 20 and 30;

So you can see quite clearly that the major part of the process is the data modelling - this is almost always the case, arrange and process your data so the code that needs to run against it is a simple as possible - forget the "I have to use Excel for my output so can only work that way" and leave that for the final transpose and report procedures to deal with.

Ask a Question
Discussion stats
  • 3 replies
  • 111 views
  • 1 like
  • 4 in conversation