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 |
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;
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.