topic Re: INDEX and PROC SQL ORDER BY and GROUP BY in SAS Programming

INDEX and PROC SQL ORDER BY and GROUP BY

Haris — Sat, 11 Jan 2020 21:08:45 GMT

I can't find any information on this: will index benefit PROC SQL code that uses ORDER BY and, especially, GROUP BY?

Re: INDEX and PROC SQL ORDER BY and GROUP BY

Haris — Sat, 11 Jan 2020 21:37:06 GMT

... and while we're on the topic, if anyone knows if INDEX would speed up procedures that run random effects models such as MIXED and GLIMMIX that block the dataset by GROUPID, please let us know.

Re: INDEX and PROC SQL ORDER BY and GROUP BY

jvdl — Sun, 12 Jan 2020 12:52:22 GMT

According to the fourth edition of the Advanced Prep Guide for SAS 9, pages 229 and 230, the following queries can be optimised by an index:

a WHERE clause expression that contains a comparison operator, the TRIM or SUBSTR function, the CONTAINS operator, the LIKE operator
a subquery returning values to the IN operator
a correlated subquery in which the column being compared with the correlated reference is indexed
a join in which the join expression contains the “=” operator and/or all the columns in the join expression are indexed in one of the tables being joined

Re: INDEX and PROC SQL ORDER BY and GROUP BY

mkeintz — Sun, 12 Jan 2020 14:51:08 GMT

@Haris wrote:

... and while we're on the topic, if anyone knows if INDEX would speed up procedures that run random effects models such as MIXED and GLIMMIX that block the dataset by GROUPID, please let us know.

If the data are indexed by GROUPID but not sorted by GROUPID, I can imagine beneficial usage of indexes if you use a "BY GROUPID" statement.

But I don't think that is what you mean by "block the dataset". I presume you want an analysis that includes some sort of between-block contrasts. In that case I don't see how indexing helps, since the entire dataset (not a subset) needs to be read in. If so, it would be faster to read the whole dataset sequentially (i.e. not in block order), and accumulate the necessary statistics in memory for each block.

Maybe there are some types of iterative maximum-likelihood estimation techniques that benefit from re-ordering the data, but I assume the iterations that requiring re-reading data use a dynamically created (and organized) utility file - at least for large data collections that can't be efficiently managed in memory.

Or am I missing the question entirely?

Re: INDEX and PROC SQL ORDER BY and GROUP BY

Haris — Sun, 12 Jan 2020 19:14:52 GMT

You got it right. Since a categorical random effects model processes the data by levels of the random variable, I am wondering if index of that random factor gets used in computation.

Re: INDEX and PROC SQL ORDER BY and GROUP BY

mkeintz — Mon, 13 Jan 2020 13:51:50 GMT

@Haris wrote:
You got it right. Since a categorical random effects model processes the data by levels of the random variable, I am wondering if index of that random factor gets used in computation.

Well, I can't say I know the answer to that question.

But I can say that I don't see any advantage in this case to indexing the random variable.

Re: INDEX and PROC SQL ORDER BY and GROUP BY

Haris — Wed, 15 Jan 2020 15:33:20 GMT

When I run PROC SQL with GROUP BY, SAS log does not contain any mention of INDEX use but a specific program runs in just under 3 minutes instead of just under 4 minutes prior to the implementation of the INDEX. Not a rigorous test but that's all I got.

Re: INDEX and PROC SQL ORDER BY and GROUP BY

yabwon — Wed, 15 Jan 2020 15:45:40 GMT

Hi,

did you try `options msglevel=i;` ?

that should display info if an index was used.

All the best

Bart

Re: INDEX and PROC SQL ORDER BY and GROUP BY

Haris — Sat, 18 Jan 2020 14:58:28 GMT

I did. There was no note about INDEX but 25% of the execution time was shaved off.