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?
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
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.