How to exclude the lowest numbers when finding mean

[Dna53582]

Hello.

I have exam scores and I need to write a SAS program in which the DATA step reads all the scores using INFILE and then excludes the lowest midterm score when calculating the mean. I need help with how to exclude the lowest midterm scores when calculating mean? Data is attached. Thanks

[Kurt_Bremser]

Please do always (as in ALWAYS) post example data as text, preferably as DATA step code with DATALINES.

Transpose your dataset to a long layout (there should be some kind of student id to use in the BY).

Sort by value, and DELETE the observations where the name contains exam and FIRST.(id) is true. Then use PROC SUMMARY to get the mean.

[PaigeMiller]

Some of us refuse to download attachments, and so we request (a portion of) the data be provided as text, and as working SAS data step code (examples and instructions)

Also, please show us  the SAS code you have tried so far.

[Rick_SAS]

Take a moment to read about various ways to find the smallest value in SAS.

I assume you want to exclude the lowest score from Exam1-Exam5, but not the Final?

You can do this computation by using the SMALLEST function to find the smallest value in the array. Subtract that value from the full sum of the array. 

data Want;
set Have;
array Exam[5] Exam1-Exam5;
/* sum of all scores, drop lowest Exam but keep Final */
Sum = sum(of Exam[*]) - smallest(1, of Exam[*]) + Final;
Average = Sum / 5;
run;

The advantage of this method is that there is no need to transpose or sort.

[Kurt_Bremser]

Shouldn't we divide by 4, after removing one element?

[mkeintz]

---

@Kurt_Bremser wrote:

Shouldn't we divide by 4, after removing one element?

---

I don't think so, since the FINAL was added to the numerator, summing the 4 highest from (EXAM1 .. EXAM5) and the final.

[Tom]

That works as long as skipped exams are recorded as zero instead of missing.

[Tom]

Most grading rubrics assign different weights to the midterm and final scores.  Some might give the final extra weight. Others give the final equal or smaller weight.

Here is a formula based solution where you can adjust the weighting when generating the overall grade.

For example we could make the final count for 60% of the overall grade.

data want;
  set have;
  excluded=-min(of exam1-exam5);
  if n(of exam1-exam5) < 5 then excluded=0;
  exams = sum(of exam1-exam5 excluded)/4; 
  final_wt=0.6;
  exam_wt=0.4;
  grade = exam_wt*exams + final_wt*final;
run;

So  with that weighting the study with the higher final score gets a better overall grade.

Obs id exam1 exam2 exam3 exam4 exam5 final excluded exams final_wt exam_wt grade

 1  1   100   100   100   100     0    90       0    100     0.6     0.4     94
 2  2    90    90    90    90    85   100     -85     90     0.6     0.4     96

If you want each of the included exams and the final to have equal weight then use FINAL_WT=.2 and EXAM_WT=.8. 

Now the student with the higher mid term scores does better than the student with the higher final score.

Obs id exam1 exam2 exam3 exam4 exam5 final excluded exams final_wt exam_wt grade

 1  1   100   100   100   100     0    90       0    100     0.2     0.8     98
 2  2    90    90    90    90    85   100     -85     90     0.2     0.8     92