BookmarkSubscribeRSS Feed
milts
Pyrite | Level 9
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
5 REPLIES 5
YueweiLiu
Calcite | Level 5
I think the MEANS procedure is nice enough for it.
SASKiwi
PROC Star
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
Tourmaline | Level 20
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
Data never sleeps
Ksharp
Super User
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
PROC Star
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 927 views
  • 0 likes
  • 5 in conversation