Your SAS programs, embedded in web apps and elsewhere

Selecting the Max Group SAS SQL.

Accepted Solution Solved
Reply
Super Contributor
Posts: 480
Accepted Solution

Selecting the Max Group SAS SQL.

I am trying to select the group with the maximum of a value.  An example of how do this in Oracle is below.  SQL server also allows this using the first(column) method.  SAS SQL does not include either of these two methods.  Do I need to write a self join to do this in SAS?  I would strongly rather not write a self-join due to it being processor inefficient.

SELECT degree_level, total

    from

        (

            select count(*) as total, degree_level

            from enrollment

            where academic_period= '201410' and college = 'MD'

            group by degree_level

            order by total desc

        )

     where rownum =1;


Accepted Solutions
Solution
‎02-27-2015 01:31 PM
Respected Advisor
Posts: 3,124

Re: Selecting the Max Group SAS SQL.

This comes close if you don't have multiple same max total,

proc sql;

SELECT *

  from

  ( select count(*) as total, degree_level

  from enrollment

  where academic_period= '201410' and college = 'MD'

  group by degree_level

/* order by total desc*/

  )

/* werhe rownum=1;*/

  having total = max(total);

quit;

But to get the EXACT the same, you would have to resort to undocumented MONOTONIC().

View solution in original post


All Replies
Solution
‎02-27-2015 01:31 PM
Respected Advisor
Posts: 3,124

Re: Selecting the Max Group SAS SQL.

This comes close if you don't have multiple same max total,

proc sql;

SELECT *

  from

  ( select count(*) as total, degree_level

  from enrollment

  where academic_period= '201410' and college = 'MD'

  group by degree_level

/* order by total desc*/

  )

/* werhe rownum=1;*/

  having total = max(total);

quit;

But to get the EXACT the same, you would have to resort to undocumented MONOTONIC().

Super Contributor
Posts: 480

Re: Selecting the Max Group SAS SQL.

Hai.kuo,

Your solution is very efficient and I all I need for this solution.

I noticed that the order by block with sub queries seems to cause errors.  I was attempting to use an into statement with the order by block.  Even if the order by solution worked in this particular case the having is much better.


When I use the into block it automatically selects the top record so if there are two records with the same total I don’t have to worry.

☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 334 views
  • 0 likes
  • 2 in conversation