how can I sum over a set of variables by ID?
Suppose to have the following:
data DB;
input ID :$20. Value1 Value2 Value3;
cards;
0001 33 . .
0001 . 22 .
0001 . . .
0001 . . 6
0002 . 32 .
0002 . . 4
0003 . 2 90
0003 3 . .
;
Is there a way to get the following?
data DB1;
input ID :$20. Value1 Value2 Value3 Total;
cards;
0001 33 . . 61
0001 . 22 . .
0001 . . . .
0001 . . 6 .
0002 . 32 . 36
0002 . . 4 .
0003 . 2 90 95
0003 3 . . .
;
The sum should be placed at the first occurrence/position of each ID.
Thank you in advance
Try this
data db1;
do until(last.ID);
set DB;
by ID;
if first.ID then Total = 0;
Total + sum(of Value:);
end;
do until(last.ID);
set DB;
by ID;
if not first.ID then Total = .;
output;
end;
run;
Result:
ID Value1 Value2 Value3 Total
0001 33 . . 61
0001 . 22 . .
0001 . . . .
0001 . . 6 .
0002 . 32 . 36
0002 . . 4 .
0003 . 2 90 95
0003 3 . . .