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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.