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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 165
Accepted Solution

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:

namedatetimeIntradaydailysum
A12/1/20139:30:001  
A12/1/201310:00:0023 
A12/2/201310:30:003  
A12/2/201311:00:0025 
A12/3/201310:00:002  
A12/3/201310:30:001  
A12/3/201311:00:0014 
A12/4/201311:30:001  
A12/4/201312:00:003  
A12/4/201312:30:0037 
A12/7/20139:30:001  
A12/7/201310:00:001221
A12/8/201310:30:002  
A12/8/201311:00:003523
A12/17/201311:30:001  
A12/17/201312:00:001223
A12/18/201312:30:002220
A12/28/20139:30:001  
A12/28/201310:00:001218
B12/8/201310:30:002  
B12/8/201311:30:002  
B12/8/201312:30:0015 
B12/14/20139:30:001  
B12/14/201310:30:0012 
B12/15/201312:00:002  
B12/15/201312:30:0013 
B12/24/20139:30:0011 
B12/25/201310:00:002  
B12/25/201310:30:002415
B12/26/201312:00:002212
B12/30/20139:30:003  
B12/30/201310:00:002  
B12/30/201310:30:001616
B1/3/201411:00:001  
B1/3/201411:30:001  
B1/13/201412:00:001316

 

 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?

Thanks in advance.


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

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

Posted in reply to aminkarimid

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;

View solution in original post


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

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

Posted in reply to aminkarimid

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.

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

Discussion stats
  • 1 reply
  • 114 views
  • 0 likes
  • 2 in conversation