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

whymath — Fri, 12 Jun 2020 10:15:00 GMT

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?

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

ed_sas_member — Fri, 12 Jun 2020 10:21:59 GMT

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,

topic Re: Is there a better way to use nested function in PROC SQL? in SAS Programming

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

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