turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- computing sum and average by every nth row

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-10-2017 12:44 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-10-2017 01:13 AM

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;

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-10-2017 02:10 AM

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