topic Re: How to exclude the lowest numbers when finding mean in SAS Programming

How to exclude the lowest numbers when finding mean

Dna53582 — Sun, 03 Sep 2023 06:38:09 GMT

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

Re: How to exclude the lowest numbers when finding mean

Kurt_Bremser — Sun, 03 Sep 2023 08:02:36 GMT

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.

Re: How to exclude the lowest numbers when finding mean

PaigeMiller — Sun, 03 Sep 2023 10:08:10 GMT

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.

Re: How to exclude the lowest numbers when finding mean

Rick_SAS — Sun, 03 Sep 2023 11:22:42 GMT

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.

Re: How to exclude the lowest numbers when finding mean

Kurt_Bremser — Sun, 03 Sep 2023 12:59:50 GMT

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

Re: How to exclude the lowest numbers when finding mean

mkeintz — Sun, 03 Sep 2023 14:57:01 GMT

@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.

Re: How to exclude the lowest numbers when finding mean

Tom — Sun, 03 Sep 2023 15:07:03 GMT

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

Re: How to exclude the lowest numbers when finding mean

Tom — Sun, 03 Sep 2023 15:30:12 GMT

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