topic Re: How to sum obs at specific date and four days before? in SAS Programming

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

aminkarimid — Sun, 10 Dec 2017 12:02:32 GMT

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?

Thanks in advance.

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

Ksharp — Sun, 10 Dec 2017 10:19:15 GMT

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;