how to get Max(avg(Variable))

[atul_desh]

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

Please help....

also please advice anyweblink/soft copy/ thru which i can improve proc sql.

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;

[RW9]

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;

[atul_desh]

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,

[Reeza]

What does not working mean?

[RW9]

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

[Rick_SAS]

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;

[atul_desh]

Thanks Rick,

but i want if it could done with proc sql;

Thanks for your support !!

[Ksharp · Accepted solution]

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;