Hi Rick,

Thanks for good reminder about "the power of WHERE"!

Small add-on, if I may. The WHERE statement, as we all know, also supports index use. But, which is less popular, a function in WHERE may sometimes "break" index use.

For example, having dataset test:

1
2 options msglevel=I fullstimer;
3 data test(index=(date));
4 do date = 0 to 3e5;
5 output;
6 end;
7 run;

NOTE: The data set WORK.TEST has 300001 observations and 1 variables.
INFO: Multiple concurrent threads will be used to create the index.
NOTE: Simple index date has been defined.

we can write below and use index:

8
9 data test1;
10 set test;
11 where date = "25jul2022"d;
INFO: Index date selected for WHERE clause optimization.
12 run;

NOTE: There were 1 observations read from the data set WORK.TEST.
WHERE date='25JUL2022'D;
NOTE: The data set WORK.TEST1 has 1 observations and 1 variables.

But when we use function:

13
14 data test2;
15 set test;
16 where date = today();
17 run;

NOTE: There were 1 observations read from the data set WORK.TEST.
WHERE date=TODAY();
NOTE: The data set WORK.TEST2 has 1 observations and 1 variables.

index is not used. But(!) in that situation the %sysfunc() macro function may help:

18
19 data test3;
20 set test;
21 where date = %sysfunc(today());
INFO: Index date selected for WHERE clause optimization.
22 run;

NOTE: There were 1 observations read from the data set WORK.TEST.
WHERE date=22851;
NOTE: The data set WORK.TEST3 has 1 observations and 1 variables.

All the best
Bart