topic calculating an average, but removing lowest value in SAS Programming

calculating an average, but removing lowest value

jack_knuettel — Sun, 03 Oct 2021 22:04:12 GMT

I have this dataset with 6 different hw grades. I ned to find the HW average, but I need the lowest value to be dropped. Here is the code:

Name HW1 HW2 HW3 HW4 HW5 HW6

Abby	95.00	85.00	69.00	93.00	81.70	85.00
Hans	95.50	56.50	77.50	50.00	79.20	75.00
Beyonce	90.00	83.00	89.00	97.00	90.80	85.00
Camila	92.50	98.00	89.00	84.00	80.00	85.00
Carlos	95.00	69.00	55.00	92.00	76.70	100.00
Darius	95.00	97.00	99.00	89.00	100.00	87.50
Terrance	93.00	92.50	95.00	68.50	82.00	95.00
Evelyn	92.50	94.00	100.00	96.00	100.00	100.00
Gertrude	95.00	96.00	100.00	97.00	92.50	100.00
Alamance	93.50	84.00	100.00	91.00	72.50	85.00
Hiromi	85.50	94.00	85.00	84.00	97.50	100.00
Isla	80.00	40.00	45.00	61.70	64.00	72.50
Jasmine	95.00	98.00	100.00	96.00	100.00	100.00
Julieta	75.00	82.00	90.00	74.50	80.80	82.50
Lavanya	85.00	79.00	85.00	96.00	100.00	82.50
Oliver	98.00	100.00	96.50	98.00	97.50	100.00
Elon	83.00	98.00	87.50	96.00	100.00	75.00
Yvette	92.50	65.00	82.50	83.30	80.00	100.00

Re: calculating an average, but removing lowest value

ChrisNZ — Sun, 03 Oct 2021 22:27:43 GMT

Lile this?

 data T;
  HW1=1;
  HW2=2;
  HW3=3;
  HW4=4;
  HW5=5;
  HW6=6;
  MIN=min(of HW1-HW6);
  AVG=mean(ifn(HW1=MIN,.,HW1)
          ,ifn(HW2=MIN,.,HW2)
          ,ifn(HW3=MIN,.,HW3)
          ,ifn(HW4=MIN,.,HW4)
          ,ifn(HW5=MIN,.,HW5)
          ,ifn(HW6=MIN,.,HW6));
run;

Re: calculating an average, but removing lowest value

PaigeMiller — Sun, 03 Oct 2021 23:21:29 GMT

Method #1:

data want;
    set have;
    avg_without_min = (6*mean(of hw1-hw6) - min(of hw1-hw6))/5;
run;

Method B:

data want;
    set have;
    call sortn(of hw1-hw6);
    avg_without_min=mean(of hw2-hw6);
run;

To @jack_knuettel

What happens if there are two values of HW1-HW6 both with the minimum value (in other words, the two are tied)? Then what?

Re: calculating an average, but removing lowest value

ChrisNZ — Sun, 03 Oct 2021 23:49:00 GMT

Or:

avg_without_min = (sum(of hw1-hw6) - min(of hw1-hw6))/(n(of hw1-hw6)-1);

Re: calculating an average, but removing lowest value

PaigeMiller — Mon, 04 Oct 2021 10:01:10 GMT

@ChrisNZ wrote:

Or:

avg_without_min = (sum(of hw1-hw6) - min(of hw1-hw6))/(n(of hw1-hw6)-1);

Certainly an improvement on my earlier Method #1. Handles missing values properly as well.

Re: calculating an average, but removing lowest value

Reeza — Mon, 04 Oct 2021 16:26:37 GMT

One more for fun, but won't scale at all.

array HW(6) HW1-HW6;

x = mean(largest(1, of HW(*)),
               largest(2, of HW(*)),
               ...
               largest(5, of HW(*));

Another method would be to transpose the data, sorting it then dropping the first record.

I'd probably use @ChrisNZ solution in reality though.