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
I think you have a > where you want < .
Do you intend:
set my_large_data (where = ( %eval(&i. - 1) < year1 < %eval(&i. + 2) ));
?
I think you have a > where you want < .
Do you intend:
set my_large_data (where = ( %eval(&i. - 1) < year1 < %eval(&i. + 2) ));
?
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!
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.
Yes, thank you. I flipped the inequality and my eyes just were not catching it. It should be sorted now.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.