Read previous and next observations

Question

I have a dataset like this(sp is an indicator):

datetime        sp
ddmmyy:10:30:00 N
ddmmyy:10:31:00 N
ddmmyy:10:32:00 Y
ddmmyy:10:33:00 N
ddmmyy:10:34:00 N

And I would like to extract observations with "Y" and also the previous and next one:

ID              sp
ddmmyy:10:31:00 N
ddmmyy:10:32:00 Y
ddmmyy:10:33:00 N

I tired to use "lag" and successfully extract the observations with "Y" and the next one, but still have no idea about how to extract the previous one.

Here is my try:

data surprise_6_step3; set surprise_6_step2;
length lag_sp $1;
lag_sp=lag(sp);
if sp='N' and lag(sp)='N' then delete;
run;

and the result is:

ID              sp
ddmmyy:10:32:00 Y
ddmmyy:10:33:00 N

Any methods to extract the previous observation also? Thx for any help.

Answer 1

Try using the point option in set statement in data step. Like this:

data extract;
set surprise_6_step2 nobs=nobs;
if sp = 'Y' then do;
  current = _N_;
  prev = current - 1;
  next = current + 1;

  if prev > 0 then do;
    set x point = prev;
    output;
  end;

  set x point = current;
  output;

  if next <= nobs then do;
    set x point = next;
    output;
  end;
end;

run;

There is an implicite loop through dataset when you use it in set statement. _N_ is an automatic variable that contains information about what observation is implicite loop on (starts from 1). When you find your value, you store the value of _N_ into variable current so you know on which row you have found it. nobs is total number of observations in a dataset.

Checking if prev is greater then 0 and if next is less then nobs avoids an error if your row is first in a dataset (then there is no previous row) and if your row is last in a dataset (then there is no next row).