topic Re: Open and closed intervals in the if statement in SAS Programming

Open and closed intervals in the if statement

deleted_user — Thu, 04 Nov 2010 09:24:40 GMT

Is there a way to use open and/or closed numeric non-integer intervals with the IF statement?

e.g. X=1.26;
if X in (1:5) then Y=1; else Y=0; results in Y=0.
if X in (1.1:5.3) then Y=1; else Y=0; returns an error.

For programming reasons I can't use the syntax
if X >= 1 and X<=5 then Y=1; else Y=0;
as I need a single condition to follow the variable X without repeating the variable name.

I note that one can use the BETWEEN operator with a WHERE statement but not with an IF statement - does anyone know the rationale behind this?

Re: Open and closed intervals in the if statement

data_null__ — Thu, 04 Nov 2010 12:24:20 GMT

It is not clear to me what values you expect from the range 1.1:5.3
What is the increment?

You can create an array of target values and use the IN operator on the array.

[pre]
1259 data _null_;
1260 array x[5];
1261 j = 1;
1262 do a=1.1 by 1 while(j le dim(x));
1263 x = a;
1264 j + 1;
1265 end;
1266 put x

;
1267 z = 2.1;
1268 z_in_x = z in x;
1269 put z_in_x=;
1270 run;

1.1 2.1 3.1 4.1 5.1
z_in_x=1
[/pre]

Re: Open and closed intervals in the if statement

deleted_user — Thu, 04 Nov 2010 13:41:19 GMT

Hi, actually what I am looking for is the equivalent of the BETWEEN operator for the IF statement (for some reason BETWEEN can only be used with the WHERE statement). i.e. "IF X in range then do Y", where X is a non-integer numeric variable. My code can't use the syntax "IF X ge z1 and x le z2 then do Y", as there must be only a single condition following the variable X.

Re: Open and closed intervals in the if statement

sbb — Thu, 04 Nov 2010 14:09:58 GMT

Consider this "expression" structure:

IF ( 0.5 LE LE 1.5 ) then....;

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic / post:

sas language data step if statement expression site:sas.com

Re: Open and closed intervals in the if statement

deleted_user — Thu, 04 Nov 2010 14:19:34 GMT

Thanks, I am looking for a condition that FOLLOWS the variable only as I am doing a lookup of a table that contains only the part after the variable. i.e. I am looking for "IF X then ...", where is kept in the lookup table. E.g. is , except you can't use BETWEEN with the IF statement.

Re: Open and closed intervals in the if statement

data_null__ — Thu, 04 Nov 2010 14:26:45 GMT

Why do have such silly rules.

Perhaps this will meet the requirement. Check the bounds in different if statements.

[pre]
1487 data _null_;
1488 do x = 0.26 to 7.26 by 1;
1489 put 'NOTE: ' x=;
1490 if x ge 1.1 then if x le 5.3
1491 then put 'NOTE- ' x= 'is in the range 1.1 to 5.3';
1492 end;
1493 run;

NOTE: x=0.26
NOTE: x=1.26
x=1.26 is in the range 1.1 to 5.3
NOTE: x=2.26
x=2.26 is in the range 1.1 to 5.3
NOTE: x=3.26
x=3.26 is in the range 1.1 to 5.3
NOTE: x=4.26
x=4.26 is in the range 1.1 to 5.3
NOTE: x=5.26
x=5.26 is in the range 1.1 to 5.3
NOTE: x=6.26
NOTE: x=7.26
[/pre]

Re: Open and closed intervals in the if statement

sbb — Thu, 04 Nov 2010 16:24:31 GMT

To the OP: did the IF example not make sense? Suggest you try it to test a value range. Otherwise explain why you don't think it will work (but without just repeating the question/post again).

Scott Barry
SBBWorks, Inc.

Re: Open and closed intervals in the if statement

chang_y_chung_hotmail_com — Thu, 04 Nov 2010 17:19:58 GMT

> Thanks, I am looking for a condition that FOLLOWS the
> variable only as I am doing a lookup of a table that
> contains only the part after the variable. i.e. I am
> looking for "IF X then ...", where
> is kept in the lookup table. E.g.
> is , except you
> can't use BETWEEN with the IF statement.
...
If the can contain the name of the variable (which will likely be the case when the lookup key is indeed the variable name), then you can do something like below. That is the part being:
[pre]
= ifn(x=0|missing(x), 999, ifn(1.3<=x<=10.7,x,-x))
[/pre]
hope this helps a bit.
[pre]
data _null_;
do x = ., 0, 1.26, 6.26, 10.71;
if x = ifn(x=0|missing(x), 999, ifn(1.3<=x<=10.7,x,-x)) then y = 1;
else y = 0;
put x= y=;
end;
run;
/* on log
x=. y=0
x=0 y=0
x=1.26 y=0
x=6.26 y=1
x=10.71 y=0
*/
[/pre]
Wait a minute. If you are *not* allowed to do a like:
[pre]
>= 1.3 and x <= 10.7
[/pre]
then, there is no hope. This cannot be done, as far as I know, that is.

Re: Open and closed intervals in the if statement

sbb — Thu, 04 Nov 2010 18:05:38 GMT

Exactly how are you doing the "lookup table" invocation at SAS compilation time - that would be an interesting point to clarify? Also, please share the exact SAS code you are executing, preferably in a SAS -generated log, not just your pasted code-piece. Clearly your original post isn't quite consistent with whatever is being attempted to resolve "on the fly" a piece of your IF statement.

Scott Barry
SBBWorks, Inc.

Re: Open and closed intervals in the if statement

deleted_user — Thu, 04 Nov 2010 18:12:46 GMT

Here is what I am trying to do. I have a lookup table of conditions for each outcome (work.test in this illustration, except much bigger in practice) and then I use a data step to generate all the SAS code required and put it into a macro variable 'all_code'. The coding is much simpler if all are 'to the right' of the outcome variable only:

data test;
length outvar condition1 condition2 condition3 condition4 $20;
outvar = "outvar1"; condition1="ge 20"; condition2="eq 1"; condition3="ne 5"; condition4=""; output;
outvar = "outvar2"; condition1=""; condition2=""; condition3="lt 20 "; condition4="eq 1"; output;
outvar = "outvar3"; condition1=""; condition2="eq 1"; condition3="gt 20 "; condition4="eq 1"; output;
run;

data _null_in_practice;
length _t $300;
retain _t condition_names1 - condition_names4;
array conditions{*} $20 condition1 - condition4;
array condition_names{1:4} $20 ("condition1" "condition2" "condition3" "condition4");
set test end=last;
if _n_ = 1 then _t="";
do i = 1 to dim(conditions);
if i=1 then _t=trim(left(_t))!!" if ";
if conditions ne "" then
_t = trim(left(_t))!!" ("!!trim(left(condition_names))!!" "
!!trim(left(conditions))!!") and";
if i = dim(conditions) then do;
_t = substr(_t,1,length(_t) - 3)!!" then "!! trim(left(outvar)) !!" = 1; else "
!! trim(left(outvar)) !! " = 0;";
end;
end;
if last then call symput('all_code',trim(left(_t)));
run;

Re: Open and closed intervals in the if statement

sbb — Thu, 04 Nov 2010 18:19:15 GMT

It's now clear that your code is not working yet. You have a reference to an array in the statement below which is illegal:

if conditions ne "" then

I expect you will see this error, given the pasted code syntax:

ERROR: Illegal reference to the array conditions.

Suggest you get your code working as a static program, then consider some alternative "more flexible?" methods (possibly more simply using macro logic).

Scott Barry
SBBWorks, Inc.

Message was edited by: sbb Message was edited by: sbb

Re: Open and closed intervals in the if statement

deleted_user — Thu, 04 Nov 2010 18:28:23 GMT

Apologies: the forum formatting didn't like square bracket references to array elements. Here it is again:

data test;
length outvar condition1 condition2 condition3 condition4 $20;
outvar = "outvar1"; condition1="ge 20"; condition2="eq 1"; condition3="ne 5"; condition4=""; output;
outvar = "outvar2"; condition1=""; condition2=""; condition3="lt 20 "; condition4="eq 1"; output;
outvar = "outvar3"; condition1=""; condition2="eq 1"; condition3="gt 20 "; condition4="eq 1"; output;
run;

data _null_in_practice;
length _t $300;
retain _t condition_names1 - condition_names4;
array conditions{*} $20 condition1 - condition4;
array condition_names{1:4} $20 ("condition1" "condition2" "condition3" "condition4");
set test end=last;
if _n_ = 1 then _t="";
do i = 1 to dim(conditions);
if i=1 then _t=trim(left(_t))!!" if ";
if conditions{i} ne "" then
_t = trim(left(_t))!!" ("!!trim(left(condition_names{i}))!!" "
!!trim(left(conditions{i}))!!") and";
if i = dim(conditions) then do;
_t = substr(_t,1,length(_t) - 3)!!" then "!! trim(left(outvar)) !!" = 1; else "
!! trim(left(outvar)) !! " = 0;";
end;
end;
if last then call symput('all_code',trim(left(_t)));
run;

Re: Open and closed intervals in the if statement

sbb — Thu, 04 Nov 2010 18:33:47 GMT

Suggest bookmarking this page as a favorite for reference - useful reminder for posting special data-strings:

http://support.sas.com/forums/thread.jspa?messageID=27609

Also, with arrays, you can use straight left/right parenthesis characters. Similar to how you can use !! instead of || (two vertical bars) for concatenation.

Are you doing a CALL SYMPUT or a CALL EXECUTE? How does the code get executed after generating the macro variable?

Lastly, I'll ask the question: What are you looking from the forum to get back with this latest shared information? BETWEEN doesn't work with IF....that's a given, but it seems as though you are unwilling to code your look-up table such that the IF statement "expression" string (when coded in parentheses as I demonstrated) must have either a single value to compare (EQ, LT, GE, etc.) or a value-range which must then be split into two components the part that goes left of the "varname" and the other component that is to the right.

Seems pretty clear to me - the look-up table must be altered to accommodate the IF/THEN logic when you have a value-range to use (which would have been used with a BETWEEN, had this been a WHERE statement/clause).

Scott Message was edited by: sbb

Re: Open and closed intervals in the if statement

chang_y_chung_hotmail_com — Thu, 04 Nov 2010 18:36:11 GMT

Scott wrote:
> Exactly how are you doing the "lookup table"
> invocation at SAS compilation time - that would be an
> interesting point to clarify?

I am not OP, so I can only guess. But it is quite easy to imagine a data step validating a lot of variables based on a lookup table with the variable name as the key that stores validating expression(s) for each variable. Below is an over-simplification (you should *not* rely on the global macro variables as the lookup table.), but illustrates the concept.
[pre]
/* validation macro looking up based on var name */
%macro inRange(var);
&&&var.cond
%mend inRange;

/* prepare lookup table */
data valid;
input var $ cond & $30.;
call symputx(catt(var,"cond"), cond, "g");
cards;
age 13 <= age <= 14
height 63 <= height <=65
;
run;

/* validation */
data _null_;
set sashelp.class;
ageOK = %inRange(age);
heightOK = %inRange(height);
if ageOK and heightOK then put name= age= height=;
run;
/* on log
Name=Henry Age=14 Height=63.5
Name=Judy Age=14 Height=64.3
*/
[/pre]

Re: Open and closed intervals in the if statement

deleted_user — Thu, 04 Nov 2010 20:54:47 GMT

Thanks guys, the purpose was to see if there was an equivalent of the 'between' operator for the if statement (possibly even enhanced to allow for open, left-open and right-open as well as closed intervals) and this is clearly *not* the case. But I found the catx function and the symputx (as opposed to symput) call routine useful things I didn't know of.

Re: Open and closed intervals in the if statement

data_null__ — Fri, 05 Nov 2010 12:02:51 GMT

Maybe I still don't understand what you're talking about, but then I don't think you do either.

To me the statements below confirm that BETWEEN is just a fancy way to write
(condition1 and condition2)

[pre]
2200 data class;
2201 set sashelp.class;
2202 where height between 59 and 65;
2203 run;

NOTE: There were 8 observations read from the data set SASHELP.CLASS.
WHERE (height>=59 and height<=65);
NOTE: The data set WORK.CLASS has 8 observations and 5 variables.

2204 data class;
2205 set sashelp.class;
2206 where 59 le height le 65;
2207 run;

NOTE: There were 8 observations read from the data set SASHELP.CLASS.
WHERE (height>=59 and height<=65);
NOTE: The data set WORK.CLASS has 8 observations and 5 variables.

2208
2209 data class;
2210 set sashelp.class;
2211 if 59 le height le 65;
2212 run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.CLASS has 8 observations and 5 variables.
[/pre]

Re: Open and closed intervals in the if statement

deleted_user — Fri, 05 Nov 2010 12:16:08 GMT

I am physically writing out the code:

"IF "!! !! !! " then " !! !! " =1; else " !! !! " = 0;"

For this I need to have a valid value of that appears after only and does not need a repetition of to form a valid IF statement.

That's the clearest way I can put it.

Re: Open and closed intervals in the if statement

sbb — Fri, 05 Nov 2010 12:44:41 GMT

SAS does not support the syntax you are demonstrating -- of course you already know that which is why you are using a DATA step to generate your SAS code.

Once again, you are pounding the sand by suggesting that, without BETWEEN support, maybe there might be a suitable resolution for you --- and I provide you an example. Now you need to revise your DATA step that generates the code, the one with your condition values, and when there is a range-value consideration, you need to have a "low" and a "high" component code piece generated.

From your nomenclature, here's what you now need to generate for a range-value:

1) identify and determine type of comparison that must be used (LOW, HIGH, EQUAL, RANGE-VALUE, etc.).
2) based on #1, construct IF / THEN logic according to the type of comparison.
3) construct/append-to SAS variable containing code.
4) *repeat above as needed*
5) complete DATA step, generate macro variable with SYMPUT - end of DATA step.
6) execute macro variable containing generated SAS code.

Other techniques to achieve the same result would be to write (using PUT statement) to a "temporary" external sequential file in your main DATA step (no macro variable involved, so there is no implicit code-piece length limit, which no one has discussed here!), and then use %INCLUDE ; to cause your generated SAS code to get compiled and executed.

Scott Barry
SBBWorks, Inc.

Scott Barry
SBBWorks, Inc.

Re: Open and closed intervals in the if statement

deleted_user — Fri, 05 Nov 2010 13:17:47 GMT

Thanks Scott, excellent thinking there. Actually I didn't know whether or not SAS supported the type of syntax I wanted, which is why I put the post up.

Because for any particular , can contain (on different rows) both conditions that don't need code modification and conditions that do need code modification, what I actually did since yesterday is:

1) text-search for " and ", " & ", " or ", " | " (with an implicit rule that there must be a space inserted in the condition cell as shown if any of these strings appears).
2) if 1) is false, proceed as given in previous post.
3) if 1) is true, break down the into three sub-strings: a) part before " and " or " or ", b) " and " or " or " c) part after " and " or "or ". Then modify the code to: "IF "

" then " " =1; else " " =0;"

An example of a that doesn't need modification is "ge 5.2". An example of one that does need above modification is "ge 5.2 and le 9.1".

Cheers,
Steve

Re: Open and closed intervals in the if statement

sbb — Fri, 05 Nov 2010 13:28:53 GMT

Exactly - then you now understand that the look-up table must have a suitable alternative construct to address the fact that "BETWEEN" is not supported in a SAS DATA step's IF/THEN construct, correct?

And, the next step would be for you to go off and make such changes - to mean that your post to this forum is complete/satisfied, correct?

Scott Barry
SBBWorks, Inc.