topic multiple or conditions in where clause in SAS Programming

multiple or conditions in where clause

mj5 — Wed, 25 Oct 2017 08:20:04 GMT

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?

Re: multiple or conditions in where clause

Kurt_Bremser — Wed, 25 Oct 2017 08:26:39 GMT

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

Re: multiple or conditions in where clause

Shmuel — Wed, 25 Oct 2017 08:46:17 GMT

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

(See @Kurt_Bremser note):

%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;

Re: multiple or conditions in where clause

RW9 — Wed, 25 Oct 2017 08:47:31 GMT

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.