topic Alternate to max function in SAS Programming

Alternate to max function

milts — Wed, 12 May 2010 05:53:44 GMT

Hi experts,

Is there an alternate(and more efficient) way on getting the max value for a particular column? currently i'm using max function on proc sql.

Thanks!
Milton

Re: Alternate to max function

YueweiLiu — Wed, 12 May 2010 09:58:20 GMT

I think the MEANS procedure is nice enough for it.

Re: Alternate to max function

SASKiwi — Mon, 17 May 2010 02:16:23 GMT

My experience has been that SQL runs faster for deriving a few basic statistics like mean, sum, max with simple grouping, even if the code is longer. There is a significant overhead just invoking the MEANS procedure so for simple statistics and single level grouping it is usually slower.

However MEANS is definitely a better choice for more complicated grouping and statistics.

Re: Alternate to max function

LinusH — Mon, 17 May 2010 07:21:22 GMT

What costs in this operation is to do a table scan to find out the maximum value. So I don't think that any technique is more efficient than any other.

In SPD Server (and other RDBMS?), if your max column is indexed, the query will get the answer by just analyse the index meta data. On large tables, this could boost performance with thousands of percent... It's a pity that this hasn't been implemented (yet) for Base SAS engine/SQL, since the information in the index is already available.

/Linus

Re: Alternate to max function

Ksharp — Mon, 17 May 2010 07:26:01 GMT

try to use proc sort;
Such as:
[pre]
proc sort data=yourdataset;
by descending yourvar;
run;
[/pre]

Good Luck.

Message was edited by: Ksharp Message was edited by: Ksharp

Re: Alternate to max function

SASKiwi — Tue, 18 May 2010 02:46:48 GMT

While Linus is correct in that no one technique is necessarily more efficient than another, I stand by my comment that the MEANS procedure has a significant invocation overhead compared to SQL.

Therefore simple summary stats in SQL like for example:

proc sql;
create table sum as
mean(age),
max(height
from sashelp.class
group by sex;

can often be more efficient than the procedure. Please note this is only worth pursuing for large datasets where you have performance issues and you do have to benchmark your methods for ultimate proof.