DATA Step, Macro, Functions and more

Summing Rows by Day

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 79
Accepted Solution

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. 

 

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

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

Re: Summing Rows by Day

Posted in reply to novinosrin

@daszlosek Forgive me for 1 if then Smiley Happy

 

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;

 

 

View solution in original post


All Replies
PROC Star
Posts: 1,844

Re: Summing Rows by Day

Posted in reply to daszlosek

Am i allowed to have one if then statement please? Smiley Happy

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

Re: Summing Rows by Day

Posted in reply to novinosrin

@daszlosek Forgive me for 1 if then Smiley Happy

 

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

Posted in reply to novinosrin
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

Posted in reply to daszlosek

LoL Thank you and have a nice day. Cheers!

Occasional Contributor
Posts: 14

Re: Summing Rows by Day

Posted in reply to novinosrin
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;
Respected Advisor
Posts: 3,167

Re: Summing Rows by Day

Posted in reply to daszlosek

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.

Need further help from the community? Please ask a new question.

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