BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
sas_user_1001
Obsidian | Level 7

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.
1 ACCEPTED SOLUTION

Accepted Solutions
Quentin
Super User

I think you have a > where you want < .

 

Do you intend:

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

?

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.

View solution in original post

4 REPLIES 4
Quentin
Super User

I think you have a > where you want < .

 

Do you intend:

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

?

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.
sas_user_1001
Obsidian | Level 7

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
Super User Tom
Super User

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
Obsidian | Level 7

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

SAS Innovate 2025: Register 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!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 4 replies
  • 834 views
  • 1 like
  • 3 in conversation