## How to sum obs at specific date and four days before?

Solved
Regular Contributor
Posts: 166

# How to sum obs at specific date and four days before?

[ Edited ]

Hello everybody;

I want to sum obs at specific day and four days before. For instance, consider the following table:

table 1:

 name date time Intraday daily sum A 12/1/2013 9:30:00 1 A 12/1/2013 10:00:00 2 3 A 12/2/2013 10:30:00 3 A 12/2/2013 11:00:00 2 5 A 12/3/2013 10:00:00 2 A 12/3/2013 10:30:00 1 A 12/3/2013 11:00:00 1 4 A 12/4/2013 11:30:00 1 A 12/4/2013 12:00:00 3 A 12/4/2013 12:30:00 3 7 A 12/7/2013 9:30:00 1 A 12/7/2013 10:00:00 1 2 21 A 12/8/2013 10:30:00 2 A 12/8/2013 11:00:00 3 5 23 A 12/17/2013 11:30:00 1 A 12/17/2013 12:00:00 1 2 23 A 12/18/2013 12:30:00 2 2 20 A 12/28/2013 9:30:00 1 A 12/28/2013 10:00:00 1 2 18 B 12/8/2013 10:30:00 2 B 12/8/2013 11:30:00 2 B 12/8/2013 12:30:00 1 5 B 12/14/2013 9:30:00 1 B 12/14/2013 10:30:00 1 2 B 12/15/2013 12:00:00 2 B 12/15/2013 12:30:00 1 3 B 12/24/2013 9:30:00 1 1 B 12/25/2013 10:00:00 2 B 12/25/2013 10:30:00 2 4 15 B 12/26/2013 12:00:00 2 2 12 B 12/30/2013 9:30:00 3 B 12/30/2013 10:00:00 2 B 12/30/2013 10:30:00 1 6 16 B 1/3/2014 11:00:00 1 B 1/3/2014 11:30:00 1 B 1/13/2014 12:00:00 1 3 16

Please consider the sum variable which is the aggregate of daily variable for that day and four days before.

For example the value 21 at 12/7/2013 is 3+5+4+7+2 values of daily column. In other words, I want to generate variable sum in table 1.

How can I do that?

Accepted Solutions
Solution
‎12-10-2017 05:54 AM
Super User
Posts: 10,689

## Re: How to sum obs at specific date and four days before?

That is not called four days before,should call fout non-missing value before.

``````data have;
infile cards expandtabs truncover;
input name \$ date \$ 	time \$	Intraday	daily;
cards;
A	12/1/2013	9:30:00	 1
A	12/1/2013	10:00:00	2	3
A	12/1/2013	10:30:00	3
A	12/2/2013	11:00:00	2	5
A	12/3/2013	10:00:00	2
A	12/3/2013	10:30:00	1
A	12/3/2013	11:00:00	1	4
A	12/4/2013	11:30:00	1
A	12/4/2013	12:00:00	3
A	12/4/2013	12:30:00	3	7
A	12/7/2013	9:30:00	1
A	12/7/2013	10:00:00	1	2
A	12/8/2013	10:30:00	2
A	12/8/2013	11:00:00	3	5
A	12/17/2013	11:30:00	1
A	12/17/2013	12:00:00	1	2
A	12/18/2013	12:30:00	2	2
A	12/28/2013	9:30:00	1
A	12/28/2013	10:00:00	1	2
B	12/8/2013	10:30:00	2
B	12/8/2013	11:30:00	2
B	12/8/2013	12:30:00	1	5
B	12/14/2013	9:30:00	1
B	12/14/2013	10:30:00	1	2
B	12/15/2013	12:00:00	2
B	12/15/2013	12:30:00	1	3
B	12/24/2013	9:30:00	1	1
B	12/25/2013	10:00:00	2
B	12/25/2013	10:30:00	2	4
B	12/26/2013	12:00:00	2	2
B	12/30/2013	9:30:00	3
B	12/30/2013	10:00:00	2
B	12/30/2013	10:30:00	1	6
B	1/3/2014	11:00:00	1
B	1/3/2014	11:30:00	1
B	1/13/2014	12:00:00	1	3
;
run;
data want;
set have;
by name;
array x{0:4} _temporary_;
if first.name then do;i=0;	call missing(of x{*});end;
if not missing(daily) then do;
i+1;
x{mod(i,5)}= daily;
end;
if i ge 5 and not missing(daily) then sum=sum(of x{*});
drop i;
run;``````

All Replies
Solution
‎12-10-2017 05:54 AM
Super User
Posts: 10,689

## Re: How to sum obs at specific date and four days before?

That is not called four days before,should call fout non-missing value before.

``````data have;
infile cards expandtabs truncover;
input name \$ date \$ 	time \$	Intraday	daily;
cards;
A	12/1/2013	9:30:00	 1
A	12/1/2013	10:00:00	2	3
A	12/1/2013	10:30:00	3
A	12/2/2013	11:00:00	2	5
A	12/3/2013	10:00:00	2
A	12/3/2013	10:30:00	1
A	12/3/2013	11:00:00	1	4
A	12/4/2013	11:30:00	1
A	12/4/2013	12:00:00	3
A	12/4/2013	12:30:00	3	7
A	12/7/2013	9:30:00	1
A	12/7/2013	10:00:00	1	2
A	12/8/2013	10:30:00	2
A	12/8/2013	11:00:00	3	5
A	12/17/2013	11:30:00	1
A	12/17/2013	12:00:00	1	2
A	12/18/2013	12:30:00	2	2
A	12/28/2013	9:30:00	1
A	12/28/2013	10:00:00	1	2
B	12/8/2013	10:30:00	2
B	12/8/2013	11:30:00	2
B	12/8/2013	12:30:00	1	5
B	12/14/2013	9:30:00	1
B	12/14/2013	10:30:00	1	2
B	12/15/2013	12:00:00	2
B	12/15/2013	12:30:00	1	3
B	12/24/2013	9:30:00	1	1
B	12/25/2013	10:00:00	2
B	12/25/2013	10:30:00	2	4
B	12/26/2013	12:00:00	2	2
B	12/30/2013	9:30:00	3
B	12/30/2013	10:00:00	2
B	12/30/2013	10:30:00	1	6
B	1/3/2014	11:00:00	1
B	1/3/2014	11:30:00	1
B	1/13/2014	12:00:00	1	3
;
run;
data want;
set have;
by name;
array x{0:4} _temporary_;
if first.name then do;i=0;	call missing(of x{*});end;
if not missing(daily) then do;
i+1;
x{mod(i,5)}= daily;
end;
if i ge 5 and not missing(daily) then sum=sum(of x{*});
drop i;
run;``````
☑ This topic is solved.