data s1 ;
input id $ dose ;
cards ;
101 22
101 23
101 24
102 43
102 23
102 11
102 22
103 22
103 22
;
i have date like see above
i want to data keep like see below
group(id) wise sum
id | dose | total |
101 | 23 | 69 |
101 | 24 | . |
101 | 22 | . |
102 | 11 | 99 |
102 | 22 | . |
102 | 23 | . |
102 | 43 | . |
103 | 22 | 44 |
103 | 22 | . |
Create a separate sums table, and do a merge with a twist:
data have;
input id $ dose;
cards;
101 22
101 23
101 24
102 43
102 23
102 11
102 22
103 22
103 22
;
run;
proc sql;
create table sums as
select id, sum(dose) as total
from have
group by id;
quit;
data want;
merge
have
sums
;
by id;
if not first.id then total = .;
run;
proc print data=want noobs;
run;
Result:
id dose total 101 22 69 101 23 . 101 24 . 102 43 99 102 23 . 102 11 . 102 22 . 103 22 44 103 22 .
Alternatively, you could reverse the order within the groups, sum and set total at last.id, and reverse the order back to original.
I am doing for you alternative part, by taking reference from @Kurt_Bremser and add little flavor of
retain.
proc sort data=s1;
by id descending dose;
run;
data s2;
set s1;
by id descending dose;
retain total ;
if first.id then total=dose;
else total+dose;
if last.id;
keep id total;
run;
data s2;
merge s2 s1;
by id;
if first.id then newvar=total;
else newvar=.;
drop total;
rename newvar=total;
run;
Another way is to use DOW. Two double do-loops get it. The presorted Data Set is assumed.
data want;
do until(last.id);
set have;
by id notsorted;
sum + dose;
end;
do until(last.id);
set have;
by id notsorted;
if not first.id then sum = .;
output;
end;
run;
HTH
Cheers
DATASP
That can also be done reading the data just once:
data want;
set s1;
by id;
_sum+dose;
if last.id then do;
sum=_sum;
_sum=0;
end;
drop _sum;
run;
You shouldn't have to write your own code to compute group sums. PROC SUMMARY will do this, then you can merge the results into the original data set.
All of the examples above where a DATA step is used to compute the sum have a potential error ... if there is a missing value in the variable to be summed, all the examples above result in a missing sum. This doesn't happen with PROC SUMMARY. (It can also be fixed in the data step if necessary).
Your observation on the presence of missing values is not true as I have used SUM statement. Here is a test:
data have;
input id $ dose;
cards;
101 22
101 23
101 .
101 24
102 43
102 23
102 11
102 22
103 22
103 .
103 22
;
run;
data want;
do until(last.id);
set have;
by id notsorted;
sum + dose;
end;
do until(last.id);
set have;
by id notsorted;
if not first.id then sum = .;
output;
end;
run;
Obs id dose sum
1 101 22 69
2 101 23 .
3 101 . .
4 101 24 .
5 102 43 99
6 102 23 .
7 102 11 .
8 102 22 .
9 103 22 44
10 103 . .
11 103 22 .
Thanks. I was not aware of that.
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.