CODEWITHSUNDEEP
sas
fly36
fly36

Reputation: 83

How to sum a variable and record the total in the last row using SAS

I have a dataset looks like the following:

Name   Number
a      1
b      2
c      9
d      6
e      5.5
Total  ???

I want to calculate the sum of variable Number and record the sum in the last row (corresponding with Name = 'total'). I know I can do this using proc means then merge the output backto this file. But this seems not very efficient. Can anyone tell me whether there is any better way please.

Upvotes: 1

Views: 2922

Answers (4)

Amir
Amir

Reputation: 1000

The following code uses the DOW-Loop (DO-Whitlock) to achieve the result by reading through the observations once, outputting each one, then lastly outputting the total:

data want(drop=tot);
  do until(lastrec);
    set have end=lastrec;
    tot+number;
    output;
  end;
  
  name='Total';
  number=tot;
  output;
run;

Upvotes: 1

Haikuo Bian
Haikuo Bian

Reputation: 906

For all of the data step solutions offered, it is important to keep in mind the 'Length' factor. Make sure it will accommodate both 'Total' and original values.

proc sql;
select max(5,length) into :len trimmed from dictionary.columns WHERE LIBNAME='WORK' AND MEMNAME='TEST' AND UPCASE(NAME)='NAME';
QUIT;

data test2;
length name $ &len;
set test end=last;
...
run;

Upvotes: 0

Shenglin Chen
Shenglin Chen

Reputation: 4554

It is easy to get by report procedure.

data have;
input Name $  Number ;
cards;
a      1
b      2
c      9
d      6
e      5.5
;

proc report data=have out=want(drop=_:);
rbreak after/ summarize ;
  compute after;
     name='Total';
  endcomp;
run;

Upvotes: 2

DCR
DCR

Reputation: 15698

you can do the following in a dataset:

data test2;
drop sum;
set test end = last;
retain sum;
if _n_ = 1 then sum = 0;
sum = sum + number;
output;
if last then do;
NAME = 'TOTAL';
number = sum;
output;
end;
run;

it takes just one pass through the dataset

Upvotes: 5

Related Questions