topic Re: Add a new row using sum and average of columns in SAS Programming

Add a new row using sum and average of columns

campjabn — Mon, 19 Oct 2020 19:36:06 GMT

I want to create a new row for each student with the sum of sums and the average of the mean. My data looks like this:

(there are more students, not just these two)

Student DomainNumChar Sum Mean

1 1 17.0000000 0.5666667
1 2 24.0000000 0.6857143
1 3 25.0000000 0.8333333
1 4 24.0000000 0.8000000
1 5 13.0000000 0.5200000
10 1 25.0000000 0.8333333
10 2 22.0000000 0.6285714
10 3 23.0000000 0.7666667
10 4 20.0000000 0.6666667
10 5 13.0000000 0.5200000

and I need it to look like this:

Student DomainNumChar Sum Mean

1 1 17.0000000 0.5666667
1 2 24.0000000 0.6857143
1 3 25.0000000 0.8333333
1 4 24.0000000 0.8000000
1 5 13.0000000 0.5200000

1 . 103.000000 .68114286
10 1 25.0000000 0.8333333
10 2 22.0000000 0.6285714
10 3 23.0000000 0.7666667
10 4 20.0000000 0.6666667
10 5 13.0000000 0.5200000

10 . 103.000000 .68304762

How would I make that happen? Thanks

Re: Add a new row using sum and average of columns

JIX — Tue, 20 Oct 2020 00:56:36 GMT

Hi campjabn,

One way I thought of is as follow if you don't have to do it in one data step:

data students;
input Student $ DomainNumChar $ Sum Mean;
cards;
1   1 17.0000000 0.5666667
1   2 24.0000000 0.6857143
1   3 25.0000000 0.8333333
1   4 24.0000000 0.8000000
1   5 13.0000000 0.5200000
10  1 25.0000000 0.8333333
10  2 22.0000000 0.6285714
10  3 23.0000000 0.7666667
10  4 20.0000000 0.6666667
10  5 13.0000000 0.5200000
; 
run;

proc sql;
create table student_sum as
select student, "." as domainnumchar, sum(sum) as sum, avg(mean) as mean, 99 as ord
from students
group by student;
quit;

data students; 
set students student_sum;
run;

proc sort data = students out = students (drop = ord);
by student ord;
run;

The following is the output:

Hope this helps somewhat!

Re: Add a new row using sum and average of columns

andreas_lds — Tue, 20 Oct 2020 06:53:36 GMT

Do you need a dataset or a report?

If you need a dataset, try

data want;
   _sum = 0;
   _mean = 0;

   do _n_ = 1 by 1 until(last.student);
      set have;
      by Student;

      output;

      _sum = sum(_sum, sum);
      _mean = sum(_mean, mean);
   end;

   call missing(DomainNumChar);
   Sum = _sum;
   Mean = _mean / _n_;
   output;

   drop _:;
run;