DATA Step, Macro, Functions and more

idxwhere

Accepted Solution Solved
Reply
Contributor
Posts: 43
Accepted Solution

idxwhere

[ Edited ]

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


Accepted Solutions
Solution
3 weeks ago
PROC Star
Posts: 8,094

Re: idxwhere

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

 

View solution in original post


All Replies
Solution
3 weeks ago
PROC Star
Posts: 8,094

Re: idxwhere

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

 

Contributor
Posts: 43

Re: idxwhere

[ Edited ]

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.  

PROC Star
Posts: 8,094

Re: idxwhere

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 131 views
  • 1 like
  • 2 in conversation