Solved
Contributor
Posts: 66

# how to get Max(avg(Variable))

I want Section, Std who is having highest Avergare Marks,

thank you so much in advance

data ds1;
input section \$ std \$Subject \$ Marks;
datalines;
A Ramesh Maths 30
A Ramesh English 40
A Ramesh Hindi 50
A Suresh Maths 60
A Suresh English 70
A Suresh Hindi 80
B Mahesh Maths 30
B Mahesh English 60
B Mahesh Hindi 50
B Kailash Maths 80
B Kailash English 90
B Kailash Hindi 90
;
proc print;
run;

proc sql;
select max(Avg(Marks) from ds1 group by std;
quit;

Accepted Solutions
Solution
‎10-26-2016 10:29 AM
Super User
Posts: 10,849

## Re: how to get Max(avg(Variable))

``````data ds1;
input section \$ std \$Subject \$ Marks;
datalines;
A Ramesh Maths 30
A Ramesh English 40
A Ramesh Hindi 50
A Suresh Maths 60
A Suresh English 70
A Suresh Hindi 80
B Mahesh Maths 30
B Mahesh English 60
B Mahesh Hindi 50
B Kailash Maths 80
B Kailash English 90
B Kailash Hindi 90
;
run;
proc sql;
select  *
from    (select SECTION,
STD,
AVG(MARKS) as AVG_MARKS
from   DS1
group by SECTION,
STD)

having  AVG_MARKS=max(AVG_MARKS);
quit;``````

All Replies
Super User
Posts: 9,840

## Re: how to get Max(avg(Variable))

Something like the below?  You haven't shown what the output should look like.

To learn SQL, if that is your other question, there are many resources online, Google them.  W3 schools for instance is a good reference.

```data ds1;
input section \$ std \$Subject \$ Marks;
datalines;
A Ramesh Maths 30
A Ramesh English 40
A Ramesh Hindi 50
A Suresh Maths 60
A Suresh English 70
A Suresh Hindi 80
B Mahesh Maths 30
B Mahesh English 60
B Mahesh Hindi 50
B Kailash Maths 80
B Kailash English 90
B Kailash Hindi 90
;
run;
proc sql;
create table WANT as
select  SECTION,
STD
from    (select SECTION,
STD,
AVG(MARKS) as AVG_MARKS
from   DS1
group by SECTION,
STD)
group by SECTION,
STD
having  AVG_MARKS=max(AVG_MARKS);
quit;```

Contributor
Posts: 66

## Re: how to get Max(avg(Variable))

I want section, Std in output who is having highest Average marks...

I'm not sure but

having  AVG_MARKS=max(AVG_MARKS) is not working

Thanks,

Super User
Posts: 23,999

## Re: how to get Max(avg(Variable))

What does not working mean?

Super User
Posts: 9,840

## Re: how to get Max(avg(Variable))

Per @Reeza, what is not working.  Are there log outputs?  What should the output look like, is it a logic problem?

SAS Super FREQ
Posts: 4,272

## Re: how to get Max(avg(Variable))

Do you need to use SQL? PROC MEANS can compute the means for each section and student. Then just sort the data or use SQL on the output statistics:

``````/* compute mean of each student in each section */
proc means data=ds1 noprint;
by section;
class std;
output mean= out=MeanOut(where=(_TYPE_=1) drop=_FREQ_);
run;

/* sort by mean marks */
proc sort data=MeanOut; by Marks; run;

proc print noobs data=MeanOut(obs=1 drop=_TYPE_); run;``````
Contributor
Posts: 66

## Re: how to get Max(avg(Variable))

Thanks Rick,

but i want if it could done with proc sql;

Solution
‎10-26-2016 10:29 AM
Super User
Posts: 10,849

## Re: how to get Max(avg(Variable))

``````data ds1;
input section \$ std \$Subject \$ Marks;
datalines;
A Ramesh Maths 30
A Ramesh English 40
A Ramesh Hindi 50
A Suresh Maths 60
A Suresh English 70
A Suresh Hindi 80
B Mahesh Maths 30
B Mahesh English 60
B Mahesh Hindi 50
B Kailash Maths 80
B Kailash English 90
B Kailash Hindi 90
;
run;
proc sql;
select  *
from    (select SECTION,
STD,
AVG(MARKS) as AVG_MARKS
from   DS1
group by SECTION,
STD)

having  AVG_MARKS=max(AVG_MARKS);
quit;``````
☑ This topic is solved.