topic Re: Removing observations before and after certain events in SAS Programming

Removing observations before and after certain events

willsilva — Tue, 15 Jan 2019 23:21:56 GMT

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.

Re: Removing observations before and after certain events

novinosrin — Tue, 15 Jan 2019 23:32:51 GMT


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;

Re: Removing observations before and after certain events

ChrisNZ — Wed, 16 Jan 2019 03:23:33 GMT

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;

Re: Removing observations before and after certain events

mkeintz — Wed, 16 Jan 2019 04:25:17 GMT

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;