10-25-2017 04:20 AM
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?
10-25-2017 04:26 AM
No. You'll have to code all separate conditions. But you can automate that coding with macro preprocessing.
20 gt win1 lt 30
is equivalent to
win1 lt 20
10-25-2017 04:46 AM
There are few ways to do it, but assuming the win should be in a range of values.
(See @KurtBremser 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;
10-25-2017 04:47 AM
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:
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.