## Summing Rows by Day

Solved
Frequent Contributor
Posts: 79

# Summing Rows by Day

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.

```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

Accepted Solutions
Solution
‎09-15-2017 03:31 PM
PROC Star
Posts: 1,844

## Re: Summing Rows by Day

@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;

end;

keep Week:;

run;

All Replies
PROC Star
Posts: 1,844

## Re: Summing Rows by Day

Am i allowed to have one if then statement please?

Solution
‎09-15-2017 03:31 PM
PROC Star
Posts: 1,844

## Re: Summing Rows by Day

@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;

end;

keep Week:;

run;

Frequent Contributor
Posts: 79

## Re: Summing Rows by Day

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!!
PROC Star
Posts: 1,844

## Re: Summing Rows by Day

LoL Thank you and have a nice day. Cheers!

Occasional Contributor
Posts: 14

## Re: Summing Rows by Day

``````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;``````
Posts: 3,167

## Re: Summing Rows by Day

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;``````
PROC Star
Posts: 1,844

## Re: Summing Rows by Day

@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;

☑ This topic is solved.