Is there a better way to use nested function in PROC SQL?

[whymath]

I am doing something needs more than once query in proc sql so I try to write code perform like nest function in data step. Here is an example: Data test have 2 variables, Group and Num.

data test;
  input Group$ Num;
  cards;
  A 10
  A 30
  B 10
  B 40
  C 30
  C 30
  ;
run;

Now I am looking for which Group has the biggest mean value. So firstly I compute mean for each Group, secondly get the max value of these means, Finnaly select the Group whose mean has the same value of the result in step 2.

proc sql;
  select Group, avg(Num) as Mean
  from test
  group by Group
  having Mean = (
    select max(mean) from (
      select avg(Num) as mean from test group by Group
    ) 
  )
  ;
quit;

Yes, I get the right answer, it's "C". But I don't like this method, it is too lengthy. The following code is wrong in syntax:

proc sql;
  select Group, avg(Num) as Mean
  from test
  group by Group
  having Mean = max(avg(Num))
  ;
quit;

But it is much beautiful, and shorter, too. Do you have any better way to do this?

[ed_sas_member · Accepted solution]

Hi @whymath 

Try this:

proc sql;
	select *
	from (select Group, avg(Num) as Mean
		  from test
		  group by Group)
	having Mean = max(Mean);
quit;

Best,