topic Re: Summing Rows by Day in SAS Programming

Summing Rows by Day

daszlosek — Fri, 15 Sep 2017 19:09:58 GMT

Hello,

I am trying to find a way to sum the totals by day (data have, below) to the total by week (data want, below) without using the if/then statement (since I have over 200 days, this would take too long). I would like to find a more efficient way to do this.

Thanks in advance for your help

data have;
input day total;
datalines;
1 48
2 29
3 39
4 46
5 35
6 44
7 41
8 46
9 27
10 45
11 38
12 40
13 37
14 80
15 50
16 43
17 54
18 48
19 40
20 68
21 105
;
run;

data want

	total
Week 1	282
Week 2	313
Week 3	408

Re: Summing Rows by Day

novinosrin — Fri, 15 Sep 2017 19:20:17 GMT

Am i allowed to have one if then statement please? 🙂

Re: Summing Rows by Day

novinosrin — Fri, 15 Sep 2017 19:28:12 GMT

@daszlosek Forgive me for 1 if then 🙂

data have;

input day total;

datalines;

1 48

2 29

3 39

4 46

5 35

6 44

7 41

8 46

9 27

10 45

11 38

12 40

13 37

14 80

15 50

16 43

17 54

18 48

19 40

20 68

21 105

;

run;

data want;

do until(last);

Week_total=0;

Week+1;

do _n_=1 by 1 until(_n_=7);

set have end=last;

Week_total+total;

if _n_=7 then output;

end;

keep Week:;

run;

Re: Summing Rows by Day

daszlosek — Fri, 15 Sep 2017 19:31:03 GMT

Ha! Ofcourse! I just meant I didn't want to do a whole bunch of if 14<= day >1 then week = "week 1" sort of thing!!

Re: Summing Rows by Day

novinosrin — Fri, 15 Sep 2017 19:34:12 GMT

LoL Thank you and have a nice day. Cheers!

Re: Summing Rows by Day

sk423 — Fri, 15 Sep 2017 19:49:47 GMT

data want(where= (wf =0) keep=day c_total wf);
set have;
wf = mod(day,7);
flag = wf ~=1;
retain c_total 0;
c_total = (c_total*flag + total);
run;

Re: Summing Rows by Day

Haikuo — Fri, 15 Sep 2017 20:00:56 GMT

Proc SQL approach:


proc sql;
create table want_sql as
select ceil(day/7) as week, sum(total) as w_total
from have
group by calculated week
order by week
;
quit;

Re: Summing Rows by Day

novinosrin — Fri, 15 Sep 2017 20:44:18 GMT

@daszlosek my apologies, I should have tested to avoid the IF THEN:

data want;

Week_total=0;

Week+1;

do _n_=1 by 1 until(_n_=7);

set have;

Week_total+total;

end;

keep Week:;

run;