Selecting the Max Group SAS SQL.

[DavidPhillips2]

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;

[Haikuo · Accepted solution]

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().

[DavidPhillips2]

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.