|
Just noticed that you want vara_for and vara_back be filled
with 0 for event=0 rows. Slight change of the code:
"else . end" change to "else 0 end".
On Wed, 29 Feb 2012 23:16:38 -0500, Ya Huang <ya.huang@AMYLIN.COM> wrote:
>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
|
