Students Score Statistics and Analysis with SAS

Much data filled in our life, and the same to education. How should we utilize the data in scholl education to reveal something which can help teachers to be aware to some risk and remind students their grade?

Foreword

For widen my outlook, I did part-time work as a Mandarin teacher for five years. During the period, lots of data of students scores which are suitable for analysis were accumulated.
In this article, I will perform the statistics and analysis via software SAS. In the last part, some conclusion will come out together with kinds of diagrams, which be validated with my teaching experience.

1.Original Data Overview

1.1 Grade which I thought

Academic Year	Grade 1	Grade 2	Grade 3
2014-2015	Primary 5	Primary 6	Year 7
2015-2016	Primary 5	Primary 6	Year 7
2016-2017	Primary 4	Primary 5
2017-2018	Primary 5	Primary 6
2018-2019	Primary 5	Primary 6

There are four terms in an academic year. However, I don’t think the data of an academic year are enough for analyzing some statistical indicators. Furthermore, there are some factors influences, such as the original data for the first second academic is uncompleted and straightforward. As a conclusion, I decide only perform the statistics on these following grade and academic years:

• 2014-2015 P5 & 2015-2016 P6
• 2016-2017 P5 & 2017-2018 P6
• 2016-2017 P4 & 2017-2018 P5 & 2018-2019 P6

Note: There are three rows above which has enough and completed original data for me to perform, and the students in the same row, during the different academic year are some student.
For instance, the 3rd row:

2016-2017 P4 & 2017-2018 P5 & 2018-2019 P6

the students who were in P4 at 2016-2017, after upgraded into the new grade, are the same student with P5 at 2017-2018. Then same for P6 at 2018-2019.

1.2 Mix Student After Upgrade into a New Grade

Although the students after upgrade into a new grade are same ones as in old grade (the transfer out and transfer in students and suspension students are the changes), but the students will be mixed and assign to new classes in the new grade randomly.

like the simple illustration in the following:

students	P5 Class
A	P5-1
B	P5-1
C	P5-2
D	P5-2

surdents after mixed and new assignment in P6 grade:

students	P5 Class
A	P6-1
B	P6-2
C	P6-1
D	P6-2

To the certain extents, this is a good mechanism, and good for student and school, such as it will let students make more new good students and rejuvenate some classed and so on.
However, it also generates some disadvantages, and the effect will be displayed later. Let’s see it in the following chapters.

ps:
School management stated the assignment after mix in the grade is base on random, as I was the one who was involved, I was clear about the fact is not like what they mention, later it will be displayed in the statistics report and diagram.
It happened to validate some conclusion. :)

1.3 Stat Items

Each term in an academic year contains:

1. Dictation Score

   Dictation in the mandarin test, which requires students to write down the mandarin characters and the corresponding English meaning and some short sentence only, can indicate the level of mandarin characters writing on some extent.

   However high score of dictation sometimes might not express student grab the ability on how to use those mandarin words in a practical sense.

   After every single lesson finished, the dictation would be executed.

2. Daily Performance Score

   The subject teacher will evaluate this score base on the daily student performance, such as the response to questions in class, whether they chat or disturb others, whether they submit homework on time, the quality of homework and etc.

   This score is subjective on different teachers, and also on the attitude of the teacher to different students, so the weight of this score cannot be too high to render the statistical indicators.

   Each term has a daily performance score.

3. Unit Test Score

   A unit is composed of several lessons, generally 2 or 3 lessons. The unit test score, which could express the extent of how student grabs the knowledge in a unit, was generated from a unit test paper which consists of various kinds of question. It can evaluate student-level objectively.

   Each term has at least one, sometimes more than one unit test.

4. Term Test Score

   Every team test paper was composed by paper1 - essay, and paper2 - which consist of various kinds of questions.

   As an extra, Term 2 and Term 4 test paper have an oral test which could evaluate the verbal ability of students.

   Each team has a term test score.

1.4 Some Notes

The passing score is 70. if the student got the score lower than 70, means not passed, then they need a remedial. No matter how many scores he or she got in the remedial, the final score recorded in the excel file is 70. However, the ceil will be remarked with a distinct colour.

2. Data Collection

As a mandarin teacher, It’s easy for me to get the original data of student score. One more reason to make it easy is I have the habit of recording it on my computer as digital assets.

This is a sample of one class original data:

Figure: Original Data in Excel

3. Data Import

3.1 prepare library

libname ss_16_17 "C:\SAS_SGS\org_data\ss_p5_2016_2017";

3.2 using Import Wizard to import data

Figure: Import Wizard in SAS

4. Data Preparation

4.1 Check Missing Score in Data Set

Although I’m the teacher who records all score, I am not sure if there is a missing score in that, so better confirm first:

data null;
	set ss_16_17.P5_1_2016_2017_pro;
	array vvv(*) _numeric_;
	do i =1 to dim(vvv);
		if vvv(i)=. then 
		do;
			put name vvv(i)= class year;
			output;
		end;
	end;
run;

and here is the log which displayed there are some missing value in the data set:

ELYSIA LOUISE PURNOMO t4_ut1=. P5-1 2016-2017
ISABEL KIRANA MARIE t4_ut1=. P5-1 2016-2017
ISABEL KIRANA MARIE t4_oral=. P5-1 2016-2017
ISABEL KIRANA MARIE t4_exam=. P5-1 2016-2017
ISABEL KIRANA MARIE t4_da_class=. P5-1 2016-2017
ISABEL KIRANA MARIE t4_exam_last=. P5-1 2016-2017
JASON JEREMIAH t2_ut1=. P5-1 2016-2017
NOTE: There were 25 observations read from the data set ss_16_17.P5_1_2016_2017_PRO.
NOTE: The data set WORK.NULL has 7 observations and 21 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.03 seconds

explore in SAS data set:

Figure: SAS Data Set

4.2 Repair the Missing Value

From the above result, we knew some student doesn’t have a specific score of dictation or unit test, even term test. Hence I decide the substitute the missing value by some particular score.

here is the logic:

if a score of the student in a specific term is missing, then the mean of all other scores in that specific term will fill the preceding missing value in the data set.

here is code:

data ss_16_17.P5_1_2016_2017_pro;
	set ss_16_17.P5_1_2016_2017_pro;

	select(name);
		when('ELYSIA LOUISE PURNOMO') do;
			t4_ut1=mean(of t4_:);
		end;
		when('JASON JEREMIAH') do;
			t2_ut1=mean(of t2_:);
		end;
		when('ISABEL KIRANA MARIE') do;
			tmp_a=round(mean(of t1:,of t2:,of t3:,of t4:),0.1);
			t4_ut1=tmp_a;
			t4_oral=tmp_a;
			t4_exam=tmp_a;
			t4_da_class=tmp_a;
			t4_exam_last=tmp_a;
		end;
		otherwise;
	end;
	
	drop tmp_a;
run;

4.3 cross-check the student name in each grade

As the preceding explanation, the students will be mixed randomly and re-assign to different classes in the new grade. So it is necessary to check whether each student name of P5 exists in P6.

here is the code:

data all_name_p5;
	set ss_16_17.P5_1_2016_2017_org_done(keep=name class year) ss_16_17.P5_2_2016_2017_org_done(keep=name class year);
run;

data all_name_p6;
	set ss_16_17.P6_1_2017_2018_org_done(keep=name class year) ss_16_17.P6_2_2017_2018_org_done(keep=name class year);
run;

proc sort data=all_name_p5;
by name;

proc sort data=all_name_p6;
by name;

data check_re;
	merge all_name_p5 (in=in_p5) all_name_p6(in=in_p6);
by name;
	if (in_p5 and not in_p6) or (not in_p5 and in_p6);
run;

As a result, I found there is a spelling typo of one student:

The name of a student “CELLINE CHANDRA PRASETYA” in P5 score list became another name “CELINE CHANDRA PRASETYA” in P6 score list, so I need to modify manually:

data ss_16_17.P5_2_2016_2017_pro;
set ss_16_17.P5_2_2016_2017_org_done;
if name='CELLINE CHANDRA PRASETYA' then name='CELINE CHANDRA PRASETYA';
run;

4.4 Converge several score in each term to one

There are several dictation scores, several unit test score, one daily performance score and one term test score in each term.

As to unify the statistical source, I decided to converge these scores to one score for each term by the following formula:

Converge Dictation Test and Unit Test first:

DT = mean(DT_score1,DT_score2,....,DT_scoreN)
UT = mean(UT_score1,UT_score2,....,UT_scoreN)
Daily_score = mean(daily_performance_score, DT)

Note:

• I combine the Dictation Test with Daily Performance Score because the dictation test in my school actually cannot indicate the actual level of students. Some students memorized the dictation word list mechanically, and even don’t know how to use those words in sentences. So I understand and treat the dictation in our school as homework instead of a test. The score of the dictation could express the attitude of the student to the homework only.

then converge totally:

Term score = weigh_ut * UT + weigh_ds * Daily_score + weigh_tt * Term_Test

here is the code of SAS macro:

%MACRO get_average_all_term (input_data=,output_data=,ratio_da_class=,ratio_ut=,ratio_exam=);

data &output_data;
	if &ratio_da_class+&ratio_ut+&ratio_exam ne 1 then do;
		put 'the weighting sum not equal 1 !!';
		stop;
	end;

	set &input_data end=last_row;
	array col_name(*) _numeric_;

	if not ((t1_da_class in col_name) and (t1_ut1 in col_name) and (t1_exam_last in col_name) and (t2_da_class in col_name) and (t2_ut1 in col_name) and (t2_exam_last in col_name) and (t3_da_class in col_name) and (t3_ut1 in col_name) and (t3_exam_last in col_name) and (t4_da_class in col_name) and (t4_ut1 in col_name) and (t4_exam_last in col_name)) then do;
		put 'ERROR!!!!please check all var, there are some missing col';
		stop;
	end;

	score_term1= t1_da_class * &ratio_da_class + t1_ut1 * &ratio_ut + t1_exam_last * &ratio_exam;
	score_term2= t2_da_class * &ratio_da_class + t2_ut1 * &ratio_ut + t2_exam_last * &ratio_exam;
	score_term3= t3_da_class * &ratio_da_class + t3_ut1 * &ratio_ut + t3_exam_last * &ratio_exam;
	score_term4= t4_da_class * &ratio_da_class + t4_ut1 * &ratio_ut + t4_exam_last * &ratio_exam;

	keep name score_term1 score_term2 score_term3 score_term4 class year;
run;

%MEND get_average_all_term;

As I used the macro to generate the overall term score for each term, I can assign the different weight for each kind of score easily. Here I assign the weight as:

Daily Score	: 10%
Unit Test 	: 40%
Term Test 	: 50%

After the weights are explicit, I invoke the macro for each class for P5 and P6:

%get_average_all_term(input_data=ss_16_17.P5_1_2016_2017_bas,output_data=ss_16_17.P5_1_2016_2017_stat_overall,ratio_da_class=0.1,ratio_ut=0.4,ratio_exam=0.5);

%get_average_all_term(input_data=ss_16_17.P5_2_2016_2017_bas,output_data=ss_16_17.P5_2_2016_2017_stat_overall,ratio_da_class=0.1,ratio_ut=0.4,ratio_exam=0.5);

%get_average_all_term(input_data=ss_16_17.P6_1_2017_2018_bas,output_data=ss_16_17.P6_1_2017_2018_stat_overall,ratio_da_class=0.1,ratio_ut=0.4,ratio_exam=0.5);

%get_average_all_term(input_data=ss_16_17.P6_2_2017_2018_bas,output_data=ss_16_17.P6_2_2017_2018_stat_overall,ratio_da_class=0.1,ratio_ut=0.4,ratio_exam=0.5);

4.5 Merge All students Score Base on Grade P5 & P6

Merge two classes score of P5 to one

data all_p5_ss;
	set P5_1_2016_2017_stat_overall 	P5_2_2016_2017_stat_overall;
run;

proc sort data=all_p5_ss;
	by name;
run;

Merge two classes score of P6 to one

data all_p6_ss;
set P6_1_2017_2018_stat_overall P6_2_2017_2018_stat_overall;
run;

proc sort data=all_p6_ss;
by name;
run;

cross-check whether some student score is missing after merge:

data test;
merge all_p5_ss(in=in_p5) all_p6_ss(in=in_p6);
by name;
if (in_p5 and not in_p6) or (not in_p5 and in_p6);
run;

Then I found there is one student score is missing after she upgrades to P6:

Figure: Missing Score

As she is my student, I am aware she transfer out to another school when P5 finished. So I don’t plan to analyze her data this time, then ignore it.

4.6 Combine 8 term scores in P5 & P6 base on each student name

Merge by students name:

data all_ss_p5_2016_2017;
	merge all_p5_ss(in=in_p5 rename=(class=class_16_17 score_term1=p5_term1 score_term2=p5_term2 score_term3=p5_term3 score_term4=p5_term4)) all_p6_ss(in=in_p6 rename=(class=class_17_18 score_term1=p6_term1 score_term2=p6_term2 score_term3=p6_term3 score_term4=p6_term4));
	by name;
	if in_p5 and in_p6;
	drop year;
run;

Adjust the sequence of some columns:

data all_ss_p5_2016_2017;
	retain  name p5_term1 p5_term2 p5_term3 p5_term4 p6_term1 p6_term2 p6_term3 p6_term4 class_16_17 class_17_18;
	set all_ss_p5_2016_2017;
run;

Add two statistical indicators: MEAN and STD

data All_ss_p5_2016_2017;
	set All_ss_p5_2016_2017;
	vmean=mean(of p5:,of p6:);
	vstd=std(of p5:,of p6:);
	format vmean 8.1 vstd 8.2;
run;

4.7 Adjust Statistical Indicator into a Table

4.7.1 adding score for distribution analysis

data ds_stat_p5_2016_2017;
	set all_ss_p5_2016_2017;
	term='p5_term1';
	score=p5_term1;
	class=class_16_17;
	output;
	
	term='p5_term2';
	score=p5_term2;
	class=class_16_17;
	output;
	
	term='p5_term3';
	score=p5_term3;
	class=class_16_17;
	output;
	
	term='p5_term4';
	score=p5_term4;
	class=class_16_17;
	output;
	
	term='p6_term1';
	score=p6_term1;
	class=class_17_18;
	output;
	
	term='p6_term2';
	score=p6_term2;
	class=class_17_18;
	output;
	
	term='p6_term3';
	score=p6_term3;
	class=class_17_18;
	output;
	
	term='p6_term4';
	score=p6_term4;
	class=class_17_18;
	output;
	
	keep term score class;
run;

The data is like below. Later we can use that data analyzing the score distribution:

Figure: adding score for distribution analysis

4.7.2 adding Mean and STD of each class

Then we need to compare and analyze some indicator between different classes, so we need to add Mean and STD for each class.

first, using PROC MEANS to get those indicators:

proc means data=ds_stat_p5_2016_2017 mean std noprint;
var score;
class term class;
output out=stat_onclass_p5_16_17 (where=(_type_=3));
run;

then, transpose to get the necessary data:

proc transpose data=stat_onclass_p5_16_17(where=(_stat_="MEAN" or _stat_="STD") drop=_type_ _freq_) out=stat_onclass_p5_16_17(drop=_name_ rename=(score1=MEAN score2=STD)) prefix=score;
by term class;
var score;
run;

At last, the data is like below. Later we can use that to perform statistics base on class:

Figure: Final Data Set

5. Data Modeling

5.1 Overall Stat of P5 & P6 with box

title 'All Ss P5 & P6 Score Stat(Vbox)';
title2 JUSTIFY=right '(P5 Year2016-2017)';
footnote JUSTIFY=right 'made by Vincent C' ;

proc sgplot data=ds_stat_p5_2016_2017;
	vbox score / category = term;
run;
quit;

The Diagram:

Figure: Overall Stat of P5 & P6 with box

5.2 P5 Score Distribution

title 'All Ss P5 Score Distribution';
title2 JUSTIFY=right '(P5 Year2016-2017)';
footnote JUSTIFY=right 'made by Vincent C' ;

PROC SGPANEL DATA=ds_stat_p5_2016_2017;
	PANELBY term / NOVARNAME SPACING = 5;
	ROWAXIS GRID;
	COLAXIS values=(65 to 105 by 5);
	histogram score / BINSTART=70 NBINS=10 BOUNDARY=lower 	DATALABEL=auto;
	density score;
	where term like 'p5%';
run;

The Diagram:

Figure: P5 Score Distribution

5.3 P6 Score Distribution

title 'All Ss P6 Score Distribution';
title2 JUSTIFY=right '(P5 Year2016-2017)';
footnote JUSTIFY=right 'made by Vincent C' ;

PROC SGPANEL DATA= ds_stat_p5_2016_2017;
	PANELBY term / NOVARNAME SPACING = 5;
	ROWAXIS GRID;
	COLAXIS values=(65 to 105 by 5);
	histogram score / BINSTART=70 NBINS=10 BOUNDARY=lower 	DATALABEL=auto;
	density score;
	where term like 'p6%';
run;

The Diagram:

Figure: P6 Score Distribution

5.4 P5 & P6 Score Distribution

title 'All Ss Score Distribution(During P5/P6)';
title2 JUSTIFY=right '(P5 Year2016-2017)';
footnote JUSTIFY=right 'made by Vincent C' ;

proc sgplot data= ds_stat_p5_2016_2017;
	histogram score / BINSTART=70 NBINS=10 BOUNDARY=lower 	DATALABEL=auto;
	density score;
run;

The Diagram:

Figure: P5 & P6 Score Distribution

5.5 Class Comparision in P5 base on Mean

title 'P5 Classes Comparasion(Mean&STD)';
title2 JUSTIFY=right '(P5 Year2016-2017)';
footnote JUSTIFY=right 'made by Vincent C' ;
proc sgplot data=stat_onclass_p5_16_17(where=(term like "p5%"));
vbarbasic term/ RESPONSE=mean group=class GROUPDISPLAY=CLUSTER;
yaxis values=(70 to 100 by 10) GRID;
scatter x=term y=std / DATASKIN=SHEEN MARKERATTRS=(symbol=DiamondFilled color=blue size=10px) y2axis DATALABEL=std DATALABELPOS=TOP group=class GROUPDISPLAY=CLUSTER;
/*dont add any where here*/
run;

The Diagram:

Figure: Class Comparision in P5 base on Mean

5.6 Class Comparision in P6 base on Mean

title 'P6 Classes Comparasion(Mean&STD)';
title2 JUSTIFY=right '(P5 Year2016-2017)';
footnote JUSTIFY=right 'made by Vincent C' ;
proc sgplot data=stat_onclass_p5_16_17(where=(term like "p6%"));
vbarbasic term/ RESPONSE=mean group=class GROUPDISPLAY=CLUSTER;
yaxis values=(70 to 100 by 10) GRID;
scatter x=term y=std / DATASKIN=SHEEN MARKERATTRS=(symbol=DiamondFilled color=blue size=10px) y2axis DATALABEL=std DATALABELPOS=TOP group=class GROUPDISPLAY=CLUSTER;
/*dont add any where here*/
run;

The Diagram:

Figure: Class Comparision in P6 base on Mean

5.7 Most unstable score

proc sql;
create view v_ss_std_mean_p5_16_17 as
select * from All_ss_p5_2016_2017
order by vstd desc;
run;
quit;

%let top_num=20;
title "P5/6 Top &top_num Range of Score Variation(Mean & STD)";
title2 JUSTIFY=right '(P5 Year2016-2017)';
footnote JUSTIFY=right 'made by Vincent C' ;
proc SGPLOT data=v_ss_std_mean_p5_16_17(obs=&top_num);
vbarbasic name / RESPONSE=vstd ;
scatter x=name y=vmean / y2axis DATALABEL=vmean DATALABELPOS=TOP;
yaxis values=(1 to 10 by 1) GRID;
y2axis values=(70 to 100 by 5);
/*dont add any where here*/
run;

The Diagram:

Figure: Most unstable score

5.8 Most stable score

proc sql noprint;
select count(*) into :t_num
from All_ss_p5_2016_2017;
quit;

%let last_num=20;
title "P5/6 Most &last_num Stable of Score Variation(Mean & STD)";
title2 JUSTIFY=right '(P5 Year2016-2017)';
footnote JUSTIFY=right 'made by Vincent C' ;
proc SGPLOT data=v_ss_std_mean_p5_16_17(firstobs=%eval(&t_num-&last_num));
vbarbasic name / RESPONSE=vstd ;
scatter x=name y=vmean / y2axis DATALABEL=vmean DATALABELPOS=TOP;
yaxis values=(0 to 2 by 0.2) GRID;
y2axis values=(70 to 100 by 5);
/*dont add any where here*/
run;

The Diagram:

Figure: Most stable score

5.9 Draw series plot for featured student

There are some students whose variation of all eight terms during two academic years are considerable, that means the score fluctuated in a wide range.

However, we must know some featured fluctuation below:

• go down and up with distinct fluctuation alternately.
• go down or go down in a stable pace gradually.

The above two featured diagrams indicated the different condition:

1. The first one means unstable score: the student might have the real knowledge, however, can not show up firmly, or maybe the student doesn’t have much real knowledge, just get the higher score with guessing or gambling-study.
2. The second one means the student improve their learning step by step; that’s a good phenomenon and vice versa.

5.9.1 go down or go down in a stable pace gradually

1. pre-process data set: add new indicator:

   Add the indicator which can express improvement or countermarch of 8 terms totally during two academic years.

   The method of calculation:

   • The score of the following term deduct the score of the previous term, such as term2 - term1.
   • accumulate all of the gaps of above steps, totally seven gaps.

    data ss_16_17.all_ss_p5_2016_2017(drop=i);
        set all_ss_p5_2016_2017;
        array arr_score(8) p5_term1-p5_term4 p6_term1-p6_term4;
        gap_trend=0;
        do i=2 to 8;
            gap_trend=gap_trend+arr_score(i)-arr_score(i-1);
        end;
    run;
   

   then, transpose the data set using for the next plot:

    proc transpose data=ss_16_17.All_ss_p5_2016_2017 out=ss_16_17.allscore4series name=term prefix=score;
    by name;
    run;
   

2. subsetting data:
   Getting the name of top 16 students order by going-up trend:

    proc sql outobs=16 noprint;
    create table ss_name as
    select name 
    from ss_16_17.allscore4series where term='gap_trend'
    order by score1 desc;
    quit;
   

   subsetting scores of above students:

    proc sql noprint;
    create table ss_16_17.tempT4series_goup as
        select allscore4series.*, (select score1 from 	ss_16_17.allscore4series where 	allscore4series.name=ss_name.name and allscore4series.term='gap_trend') as trend
    from ss_16_17.allscore4series,ss_name
    where allscore4series.name=ss_name.name and allscore4series.term like "p__term_"
    order by trend,term;
    quit;
   

3. Plot:

   gather plot of all students in a panel:

    PROC SGPANEL DATA=ss_16_17.tempT4series_goup noautolegend;
    PANELBY trend name  / NOVARNAME COLUMNS= 4 ONEPANEL;
    series x=term y=score1 / GROUP=name datalabel;
    run;
   

   the figure:

   

   Figure: gather in a panel

   After we glimpse at the resulting figure, we could be aware which sub-figure is what we want quickly. In this case, I spot the expectation using a red star.
   We can check and analyze those target students carefully with more details.

4. plot for going down parts:

   same procedures with the above ones, just change the order sequence by using ascending:

    proc sql outobs=16 noprint;
    create table ss_name as
    select name 
    from ss_16_17.allscore4series where term='gap_trend'
    order by score1;
    quit;
   

   the resulting figure like the following:

   

   Figure: plot for going down parts

   same with above, I spot target students using the red star as well.

5.9.2 go down and up with distinct fluctuation alternately

Because there were already stand deviation in the table, we can directly use them. Then we start with subsetting:

1. subsetting data:
   getting the name of top 16 students order by STD:

    proc sql outobs=16 noprint;
    create table ss_name as
    select name 
    from ss_16_17.allscore4series where term='vstd'
    order by score1 desc;
    quit;
   

   subsetting scores of above students:

    proc sql noprint;
    create table ss_16_17.tempT4variation as
    select allscore4series.*, (select score1 from ss_16_17.allscore4series where allscore4series.name=ss_name.name and allscore4series.term='vstd') as std
    from ss_16_17.allscore4series,ss_name
    where allscore4series.name=ss_name.name and allscore4series.term like "p__term_"
    order by std,term;
    quit;
   

2. Plot:

   gather plot of all students in a panel:

    PROC SGPANEL DATA=ss_16_17.tempT4variation noautolegend;
    PANELBY std name  / NOVARNAME COLUMNS= 4 ONEPANEL;
    series x=term y=score1 / GROUP=name datalabel;
    run;
   

   

   Figure: gather in a panel

   same with above, I spot target students using the red star as well.