BookmarkSubscribeRSS Feed
Dna53582
Calcite | Level 5

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

PaigeMiller
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
Rick_SAS
SAS Super FREQ

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.

mkeintz
PROC Star

@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

--------------------------
Tom
Super User Tom
Super User

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

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

 

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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