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