DATA Step, Macro, Functions and more

computing sum and average by every nth row

Reply
Occasional Contributor
Posts: 6

computing sum and average by every nth row

Hi, I would like to compute sum ( bal_rev = R ) and average (bal_rev = B) by every 3 period for each state.  Could you please help ? Thanks ! 

data have;
input State $ Period Bal_Rev $ AMOUNT;
datalines;
CA 1 B 1464.48
CA 2 B 1275.81
CA 3 B 943.28
CA 4 B 849.82
CA 5 B 752.29
CA 6 B 661.56
CA 1 R 4.86
CA 2 R 4.86
CA 3 R 4.3
CA 4 R 3.57
CA 5 R 3.19
CA 6 R 2.97
NV 1 B 1047.84
NV 2 B 1045.54
NV 3 B 1092.74
NV 4 B 1110.41
NV 5 B 1134.34
NV 6 B 1105.86
NV 1 R 4.91
NV 2 R 6.09
NV 3 R 6.8
NV 4 R 5.84
NV 5 R 7.22
NV 6 R 10.16
UT 1 B 9856.37
UT 2 B 9825.48
UT 3 B 9701.09
UT 4 B 9497.63
UT 5 B 9448.09
UT 6 B 9293.51
UT 1 R 13.09
UT 2 R 12.7
UT 3 R 11.35
UT 4 R 8.14
UT 5 R 16.72
UT 6 R 16.19
;
run;

data want;;
set have;
if bal_rev ='B' then do;
/* average value for every 3 period for each state */
end;
if bal_rev = 'R' then do;
/* sum value for every 3 period for each state */
end;
run;

 

Contributor
Posts: 56

Re: computing sum and average by every nth row

proc sql;
select state,qtr,bal_rev,avg(amount) as Avg_amount,sum(amount) as sum_amount from
(select *,
case when period in (1,2,3) then 'qtr1'
when period in (4,5,6) then 'qtr2'
end as qtr from have)
group by bal_rev,state,qtr;
quit;
PROC Star
Posts: 162

Re: computing sum and average by every nth row

If your sample data looks "exactly like" what you presented, here is one easy way:

data want;
set have;
sum+amount;
count+1;
if mod(period,3)=0 then do;
average=divide(sum,count);
output;
sum=0;
average=0;
count=0;
end;
drop count;
run;

 

Regards,

Naveen Srinivasan

 

 

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