Conditional Loop Question

[sas_user_1001]

I am attempting to create a loop that will capture my data at various window sizes for further evaluation. The problem, at the moment, is that the 'where' statement is only evaluating my first condition, and not the second.

Here's the code:

%let min_year = 1995

%let max_year = 2005

%macro loop;

%local window; %let window = 1;

%do i = &min_year. % to &max_year. %by 1;

data small_data_roll_wind;

set my_large_data (where = (%eval(&i. - 1) > year1 < (%eval(&i. + 2))));

run;

%let window = %eval(&window. + 1);

%end;

%mend loop;

%loop

For the most part this works, except that only "(where = (%eval(&i. - 1) > year1" is evaluated. The second component "year1 < (%eval(&i. + 2))));" seems to be completely ignored from what I can tell. I assume I am missing some syntax here, but any insight is appreciated.

[Quentin · Accepted solution]

I think you have a > where you want < .

Do you intend:

set my_large_data (where = ( %eval(&i. - 1) < year1 < %eval(&i. + 2) ));

?

[sas_user_1001]

You are so very right -- I flipped the inequality and didn't even notice after looking at this for hours. Thanks for being my second set of eyes today!

[Tom]

Your coding is convoluted.  Normal SAS statements can do arithmetic so need to have the %EVAL() in the WHERE= option.  Or to use a WHERE= option instead of just a normal WHERE statement for that matter.

But you logic is also strange.  You asked for numbers that are both less than X-1 and less than X+2.  So that is just the numbers that are less than X-1.

I suspect you meant the numbers between the two instead? Which would be those equal to X or X+1.

So you want a where statement like:

data small_data_roll_wind;
  set my_large_data ;
  where (&i. - 1) < year1 < (&i. + 2);
run;

Or perhaps if you are querying a database with an index you might want to use IN operator instead?

where year1 in (&i %eval(&i. + 1));

Depends on what functions allow you to take advantage of the index to improve performance.

[sas_user_1001]

Yes, thank you. I flipped the inequality and my eyes just were not catching it. It should be sorted now.