Quartz | Level 8

## 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
1 ACCEPTED SOLUTION

Accepted Solutions
Tourmaline | Level 20

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

7 REPLIES 7
Tourmaline | Level 20

## Re: Summing Rows by Day

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

Tourmaline | Level 20

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

Quartz | Level 8

## 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!!
Tourmaline | Level 20

## Re: Summing Rows by Day

LoL Thank you and have a nice day. Cheers!

Obsidian | Level 7

## 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;
Onyx | Level 15

## 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;
Tourmaline | Level 20

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

Discussion stats
• 7 replies
• 1313 views
• 9 likes
• 4 in conversation