adding observation for calculation

Reply
Contributor
Posts: 24

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

 

Please guide about same.

 

Regards,

Rajesh

 

Super User
Super User
Posts: 7,392

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: 858

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;

Ask a Question
Discussion stats
  • 2 replies
  • 219 views
  • 0 likes
  • 3 in conversation