topic Re: how to get Max(avg(Variable)) in SAS Programming

how to get Max(avg(Variable))

atul_desh — Wed, 26 Oct 2016 12:57:50 GMT

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;

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

RW9 — Wed, 26 Oct 2016 13:08:32 GMT

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;

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

atul_desh — Wed, 26 Oct 2016 13:17:05 GMT

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,

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

Reeza — Wed, 26 Oct 2016 13:27:49 GMT

What does not working mean?

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

Rick_SAS — Wed, 26 Oct 2016 13:40:41 GMT

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;

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

atul_desh — Wed, 26 Oct 2016 13:51:41 GMT

Thanks Rick,

but i want if it could done with proc sql;

Thanks for your support !!

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

RW9 — Wed, 26 Oct 2016 13:55:03 GMT

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

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

Ksharp — Wed, 26 Oct 2016 14:17:23 GMT

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;