create table base1 as select semester,stud,mean(new_GPA) as mean_GPA,sum(score) as sum_score,count(*) as num_course from base group by semester,stud;
quit;
proc sql;
create table base2 as select semester,stud,sum(score) as real_sum_score from (select * from base where new_GPA>0 ) group by semester,stud;
quit;
proc sql;
create table base3 as select base1.semester,base1.stud,mean_GPA,sum_score,real_sum_score,num_course from base1 join base2 on base1.semester=base2.semester and base1.stud=base2.stud;
quit;
data base4;
set base3;
if sum_score<=26 then grade_stud=1;
else if 27=<sum_score<=56 then grade_stud=2;
else if 57=<sum_score<=86 then grade_stud=3;
else if 87=<sum_score then grade_stud=4;
run;
proc sql;
create table base5 as select semester,stud,abb_course,count(*) as repeat_num from base where calculated repeat_num>1;
quit;
/*ods输出*/
ods listing close;
ods html file='C:\Users\Administrator\Desktop\htm.htm';