## How to exclude the lowest numbers when finding mean

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

7 REPLIES 7

## Re: How to exclude the lowest numbers when finding mean

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

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.

--
Paige Miller

## Re: How to exclude the lowest numbers when finding mean

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

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

## Re: How to exclude the lowest numbers when finding mean

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

## Re: How to exclude the lowest numbers when finding mean

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

## Re: How to exclude the lowest numbers when finding mean

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;
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

```

Discussion stats
• 7 replies
• 392 views
• 3 likes
• 6 in conversation