Alternate to max function

[milts]

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

[YueweiLiu]

I think the MEANS procedure is nice enough for it.

[SASKiwi]

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.

[LinusH]

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

[Ksharp]

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

[SASKiwi]

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.