Wednesday, May 11, 2011

SUM within subgroup

/* Within the subgroup of ID, if seq is the same, then calculate the sum of unit for the same seq;*/

/* Else, if seq is different then keep the value of unit */

options formdlim='-';

data test;

input ID seq unit;

cards;

100 1 10

100 2 10

101 1 10

101 1 20

103 1 10

103 2 10

104 1 10

104 1 20

;

run;

/* First is to use DATA STEP */

proc sort data=test;

by id seq;

run;

data want (keep=id seq sumunit);

set test;

by id seq;

if first.id or first.seq then sumunit=unit;

else sumunit+unit;

if last.id or last.seq then output;

run;

proc print data=want;

run;

/* Second is PROC SQL with GROUP BY */

proc sql;

select id, seq, sum(unit) as s

from test

group by id, seq

order by id, seq;

run;

quit;

No comments:

Post a Comment