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
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
proc sql;
create table want as select
formid
,max_score
,sum(max_score) as total
from have
group by formid;
quit;
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
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
Thank you all!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.