topic Re: Selecting the Max Group SAS SQL. in Developers

Selecting the Max Group SAS SQL.

DavidPhillips2 — Fri, 27 Feb 2015 17:39:47 GMT

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;

Re: Selecting the Max Group SAS SQL.

Haikuo — Fri, 27 Feb 2015 18:31:41 GMT

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

Re: Selecting the Max Group SAS SQL.

DavidPhillips2 — Fri, 27 Feb 2015 18:46:19 GMT

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.