topic Re: sum until in SAS Programming

sum until

dustychair — Wed, 10 Aug 2022 17:18:29 GMT

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 3

want:

formid max_score total

aaa1 1 3

aaa1 0 3

aaa1 2 3

aaa2 1 5

aaa2 3 5

Re: sum until

PaigeMiller — Wed, 10 Aug 2022 17:23:53 GMT

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

Re: sum until

Reeza — Wed, 10 Aug 2022 17:24:09 GMT

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

Re: sum until

PeterClemmensen — Wed, 10 Aug 2022 17:27:50 GMT

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

Re: sum until

dustychair — Thu, 11 Aug 2022 19:53:21 GMT

Thank you all!