BookmarkSubscribeRSS Feed
mj5
Obsidian | Level 7 mj5
Obsidian | Level 7

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?

3 REPLIES 3
Kurt_Bremser
Super User

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

 

Shmuel
Garnet | Level 18

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

(See @Kurt_Bremser 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;

        

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 832 views
  • 1 like
  • 4 in conversation