My table has some leading and trailing observations that I am trying to remove. I want to remove the rows that come before every 'begin' event and after every 'end' event for every single group. The table resembles the attached. Basically, I want to remove rows 1, 2, 7, 8, and 12.
data have;
input Row Time Group Event $ Value;
cards;
1 1 1 NA 0
2 2 1 NA 0
3 3 1 Begin 2.2
4 4 1 NA 3.4
5 5 1 NA 2.7
6 6 1 End 3
7 7 1 NA 1
8 1 2 NA 0
9 2 2 Begin 5.5
10 3 2 NA 3.3
11 4 2 End 2.4
12 5 2 NA 0
;
data want;
set have;
retain k 0;
if event='Begin' then k=1;
else if event='NA' and lag(event)='End' then k=0;
if k;
drop k;
run;
Another way:
data WANT;
set HAVE;
if EVENT='Begin' then KEEPIT+1;
if KEEPIT then output;
if EVENT='End' then KEEPIT=0;
drop KEEPIT;
run;
You can loop through a sequence of records until an END event, but only output after encountering the BEGIN:
data have;
input Row Time Group Event $ Value;
cards;
1 1 1 NA 0
2 2 1 NA 0
3 3 1 Begin 2.2
4 4 1 NA 3.4
5 5 1 NA 2.7
6 6 1 End 3
7 7 1 NA 1
8 1 2 NA 0
9 2 2 Begin 5.5
10 3 2 NA 3.3
11 4 2 End 2.4
12 5 2 NA 0
;
data want (drop=_begin_found);
do until (event='End ');
set have;
if Event='Begin' then _begin_found='Y';
if _begin_found='Y' then output;
end;
run;
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.