BookmarkSubscribeRSS Feed
Calcite | Level 5



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



Super User

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.

Diamond | Level 26

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

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;

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

Jade | Level 19

@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

Super User Tom
Super User

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

Super User Tom
Super User

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; 
  grade = exam_wt*exams + final_wt*final;

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




Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 6 in conversation