topic Re: Consecutive sum of flagged events in SAS Programming

Consecutive sum of flagged events

ScoobyDO — Sat, 21 May 2022 04:54:55 GMT

Hello,

I want to know how do I calculate the consecutive flagged observations and create an event.

So lets say in this example for ID 1 we see that we have flags at 2 and 3 OBS that are consecutive and hence the event would be 2 days long. Similarly in id 2, we have 2 events, 1 st one from obs 7 to 9 which is 3 day long and another is obs 12 to 13 which is 2 day long. The total length of event is 5 days.

Can someone help me with this?

obs	id	flag	Count day
1	1	0	0
2	1	1	1
3	1	1	2
4	1	0	0
5	1	1	1
6	2	0	0
7	2	1	1
8	2	1	2
9	2	1	3
10	2	0	0
11	2	0	0
12	2	1	1
13	2	1	2

Required output

id	Total event	Total Length of event
1	2	3
2	2	5

Re: Consecutive sum of flagged events

Astounding — Sat, 21 May 2022 05:25:10 GMT

Assuming your data is sorted as shown, here is how to count days and events:

data want;
   set have;
   by id flag notsorted;
   if first.id then do;
      event_count=0;
      days_count=0;
   end;
   if flag=1 then do;
      days_count + 1;
      if first.flag then event_count + 1;
   end;
   if last.id;
   keep id event_count days_count;
run;

Re: Consecutive sum of flagged events

ballardw — Sat, 21 May 2022 05:28:13 GMT

Untested code.

data want; 
   set have;
   by id flag notsorted;
   retain totalevents lengthevents;
   if first.id then call missing(totalevents,lengthevents);
   if first.flag and flag=1 then totalevents+1;
   if flag=1 then lengthevents+1;
   if last.id then output;
   keep id totalevents lenghtevents;
end;

Example data is best presented in the form of a data step code pasted into a text or code box opened on the forum with the </> or "running man" icons at the top of the message windo. Failing that as plain text pasted into a text box.

The form you posted is too ugly to fix when attempting to create a data step.

The above code using by group processing has SAS create automatic variables First and Last for each variable that are 1/0 or true/false valued so can be used to tell when when something starts/ends a by group. So can be used to reset the running totals for the first of each ID group and increment the count when the first of a Flag group is encountered. The NOTSORTED on the By statement allows use of the By for the Flag that is not strictly in sort order.

The Retain keeps values of the variables across the data step boundary for later conditional addition to the running total.

@ScoobyDO wrote:

Hello,

I want to know how do I calculate the consecutive flagged observations and create an event.

So lets say in this example for ID 1 we see that we have flags at 2 and 3 OBS that are consecutive and hence the event would be 2 days long. Similarly in id 2, we have 2 events, 1 st one from obs 7 to 9 which is 3 day long and another is obs 12 to 13 which is 2 day long. The total length of event is 5 days.

Can someone help me with this?

obs

id

flag

Count day

1

1

0

0

2

1

1

1

3

1

1

2

4

1

0

0

5

1

1

1

6

2

0

0

7

2

1

1

8

2

1

2

9

2

1

3

10

2

0

0

11

2

0

0

12

2

1

1

13

2

1

2

Required output

id

Total event

Total Length of event

1

2

3

2

2

5

Re: Consecutive sum of flagged events

ScoobyDO — Sat, 21 May 2022 05:34:09 GMT

This works !
Thank you so much.