| Date: | Wed, 29 Feb 2012 23:16:38 -0500 |
|---|
| Reply-To: | Ya Huang <ya.huang@AMYLIN.COM> |
|---|
| Sender: | "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU> |
|---|
| From: | Ya Huang <ya.huang@AMYLIN.COM> |
|---|
| Subject: | Re: Rolling Sum with uneven intervals |
|---|
|
Here is one:
data have;
input ID Time :time. Event VarA;
format time time.;
cards;
1 11:30 0 12
2 11:32 0 13
3 11:36 0 15
4 11:45 0 10
5 11:58 0 6
6 12:02 1 5
7 12:12 0 8
8 12:16 1 9
9 12:17 0 7
10 12:22 0 5
11 12:29 1 6
12 12:32 0 9
13 12:35 0 10
;
options ls=100;
proc sql;
select distinct a.*,
sum(case when a.event=1 and
0< a.time - b.time <= '00:30't then
b.vara else . end) as vara_back,
sum(case when a.event=1 and
-'00:30't <= a.time - b.time < 0 then
b.vara else . end) as vara_for
from have a, have b
group by a.id
order by a.id,a.time
;
ID Time Event VarA vara_back vara_for
--------------------------------------------------------
1 11:30:00 0 12 . .
2 11:32:00 0 13 . .
3 11:36:00 0 15 . .
4 11:45:00 0 10 . .
5 11:58:00 0 6 . .
6 12:02:00 1 5 44 44
7 12:12:00 0 8 . .
8 12:16:00 1 9 19 37
9 12:17:00 0 7 . .
10 12:22:00 0 5 . .
11 12:29:00 1 6 34 19
12 12:32:00 0 9 . .
13 12:35:00 0 10 . .
HTH
Ya
On Wed, 29 Feb 2012 20:44:31 -0500, Randy <randistan69@HOTMAIL.COM> wrote:
>Dear All:
>
>My Data set is as follows:
>
>
>ID Time Event VarA
>1 11:30 0 12
>2 11:32 0 13
>3 11:36 0 15
>4 11:45 0 10
>5 11:58 0 6
>6 12:02 1 5
>7 12:12 0 8
>8 12:16 1 9
>9 12:17 0 7
>10 12:22 0 5
>11 12:29 1 6
>12 12:32 0 9
>13 12:35 0 10
>
>I want to construct VarA_Back and VarA_forward which sums up VarA for a
time
>LE 30 minutes pre and post the Event.
>
>So my data set should look like:
>
>ID Time Event VarA VarA_Back VarA_Forward
>1 11:30 0 12 0 0
>2 11:32 0 13 0 0
>3 11:36 0 15 0 0
>4 11:45 0 10 0 0
>5 11:58 0 6 0 0
>6 12:02 1 5 44 44
>7 12:12 0 8 0 0
>8 12:16 1 9 19 37
>9 12:17 0 7 0 0
>10 12:22 0 5 0 0
>11 12:29 1 6 34 19
>12 12:32 0 9 0 0
>13 12:35 0 10 0 0
>
>Please Help
>
>Randy
| 
