topic Re: computing sum and average by every nth row in SAS Programming

computing sum and average by every nth row

octrout — Mon, 10 Apr 2017 04:44:51 GMT

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;

Re: computing sum and average by every nth row

lakshmi_74 — Mon, 10 Apr 2017 05:13:41 GMT

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;

Re: computing sum and average by every nth row

novinosrin — Mon, 10 Apr 2017 06:10:42 GMT

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