DATA Step, Macro, Functions and more

how to get Max(avg(Variable))

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

how to get Max(avg(Variable))

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;


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

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,430

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: 60

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: 17,958

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

What does not working mean?

Super User
Super User
Posts: 7,430

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: 3,488

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: 60

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

Thanks Rick,

 

but i want if it could done with proc sql;

 

Thanks for your support !!

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

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 303 views
  • 3 likes
  • 5 in conversation