sum until

[dustychair]

Hi all,

I want to sum rows until a new formid shows up and then write this sum in a new column along the same formid. I looked at some examples if "sum until" but not sure how to apply them to my case. I provided the data set that i have and want below. Thanks in advance for any contribution.

data have

formid max_score

aaa1 1

aaa1 0

aaa1 2

aaa2 1

aaa2 1

aaa2 3

want:

formid max_score total

aaa1 1 3

aaa1 0 3

aaa1 2 3

aaa2 1 5

aaa2 1 5 

aaa2 3 5

[PaigeMiller]

proc sql;
    create table want as select 
        formid
        ,max_score
        ,sum(max_score) as total
    from have 
    group by formid;
quit;

[Reeza · Accepted solution]

Sum until implies a running total, but it seems you just want the total added to the data set. The easiest method is via SQL.

Otherwise, other approaches are outlined here:

https://github.com/statgeek/SAS-Tutorials/blob/master/add_average_value_to_dataset.sas

proc sql;
create table want as
select *, sum(max_score) as total
from have 
group by formid;
quit;

---

@dustychair wrote:

Hi all,

I want to sum rows until a new formid shows up and then write this sum in a new column along the same formid. I looked at some examples if "sum until" but not sure how to apply them to my case. I provided the data set that i have and want below. Thanks in advance for any contribution.

data have

formid max_score

aaa1 1

aaa1 0

aaa1 2

aaa2 1

aaa2 1

aaa2 3

want:

formid max_score total

aaa1 1 3

aaa1 0 3

aaa1 2 3

aaa2 1 5

aaa2 1 5 

aaa2 3 5

---

[PeterClemmensen]

data have;
input formid $ max_score;
datalines;
aaa1 1
aaa1 0
aaa1 2
aaa2 1
aaa2 1
aaa2 3
;

data want;
   do until (last.formid);
      set have;
      by formid;
      total = sum(total, max_score);
   end;
   do until (last.formid);
      set have;
      by formid;
      output;
   end;
run;

Result:

formid  max_score  total
aaa1    1          3 
aaa1    0          3 
aaa1    2          3 
aaa2    1          5 
aaa2    1          5 
aaa2    3          5 

[dustychair]

Thank you all!