Does idxwhere override where conditions that can be optimized and compound optimized from the link below?
Could it force an index to be used when the where includes an or condition with different variables?
Ex. where x in (some list) or y in (some list);
https://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a000440261.htm
It can, depending upon how it's used. According to the documentation you pointed to:
The IDXWHERE= data set option overrides the software's decision regarding whether to use an index to satisfy the conditions of a WHERE expression as follows:
IDXWHERE=YES tells SAS to decide which index is the best for optimizing a WHERE expression, disregarding the possibility that a sequential search of the data file might be more resource efficient.
IDXWHERE=NO tells SAS to ignore all indexes and satisfy the conditions of a WHERE expression by sequentially searching the data file.
Using an index to process a BY statement cannot be overridden with IDXWHERE=.
Art, CEO, AnalystFinder.com
It can, depending upon how it's used. According to the documentation you pointed to:
The IDXWHERE= data set option overrides the software's decision regarding whether to use an index to satisfy the conditions of a WHERE expression as follows:
IDXWHERE=YES tells SAS to decide which index is the best for optimizing a WHERE expression, disregarding the possibility that a sequential search of the data file might be more resource efficient.
IDXWHERE=NO tells SAS to ignore all indexes and satisfy the conditions of a WHERE expression by sequentially searching the data file.
Using an index to process a BY statement cannot be overridden with IDXWHERE=.
Art, CEO, AnalystFinder.com
The part that is confusing about the documentation is that SAS is still deciding which index is best. So, I'm not sure what rules it uses when idxwhere=yes, if not the same rules it uses without that option.
I also wasn't sure if "the software's decision regarding whether to use an index" is referring to those optimization rules.
The option can be used to force SAS to use an index, irrespective of whether its optimization routine suggests that it should or shouldn't. The idxname option can be used to specify a particular index to use.
Art, CEO, AnalystFinder.com
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.