Contributor
Posts: 63

# adding observation for calculation

Hi,

I have dataset as below.

Subject  repeat Score

A           1           .

A           2           3

A           3          8

A           4           .

B           1           6

B           2           5

B           3          3

B          4           .

My requirement is to calculate sum of scores include missing values and i want to add new row at the end of each subject .So my new dataset will look like as below

Subject  repeat Score   Total

A           1           .           0

A           2           3          3

A           3          8          11

A           4           .           11

sum=                                    11

B           1           6          6

B           2           5          11

B           3          3           14

B          4           .            14

sum=                               14

Regards,

Rajesh

Super User
Posts: 9,599

## Re: adding observation for calculation

Well, its really a twofold problem.  First adding the extra variable and then the sum row.  Try something like (note I have not tested this as I don't have time to type in the test data - provide it in the form of a datastep in future please):

```data want;
set have;
by subject;
retain total;
if first.subject then total=0;
total=total+score;
if last.subject then do;
output;
subject="sum=";
output;
end;
else output;
run;```
Valued Guide
Posts: 863

## Re: adding observation for calculation

Here is a solution:

data have;
infile cards;
input Subject\$  Repeat Score;
cards;
A           1           .
A           2           3
A           3           8
A           4           .
B           1           6
B           2           5
B           3           3
B           4           .
;

data want;
set have;
by subject;
total + score;
if first.subject then total = score;
if last.subject then do;output;
subject = 'Sum='; call missing(repeat,score);
end;
output;
run;

Discussion stats
• 2 replies
• 273 views
• 0 likes
• 3 in conversation