Last Changed row +1 in a group

Question

I've got group data and it has flags created anytime a name is changed within that group. I can pull the last two or first two observations within the group, but I am struggling figuring out how to pull the last observation with a name change AND the row right after.

The below code give me the first or last two observations per group, depending on how I sort the data.

DATA LastTwo;
    SET WhatIveGot;
    count + 1;
    BY group_ID /*data pre sorted*/;
    IF FIRST.group_ID THEN count=1;
    IF count<=2 THEN OUTPUT;
RUN;

What I need is to be the LAST observation with a name change AND the following row.

 group_ID    NAME    DATE         NAME_CHange
     1       TOM     1/1/19            0
     1       Jill    1/30/19           1
     1       Jill    1/20/19           0
     1       Bob     2/10/19           1
     1       Bob     2/30/19           0
     2       TOM     2/1/19            0
     2       Jill    2/30/19           1
     2       Jill    2/20/19           0
     2       Jim     3/10/19           1
     2       Jim     3/30/19           0
     2       Jim     4/15/19           0
     3       Joe     2/20/19           0
     3       Kim     3/10/19           1
     3       Kim     3/30/19           0
     3       Ken     4/15/19           1
     4       Tim     3/10/19           0
     4       Tim     3/30/19           0

The desired output:

 group_ID    NAME    DATE         NAME_CHange
     1       Bob     2/10/19           1
     1       Bob     2/30/19           0
     2       Jim     3/10/19           1
     2       Jim     3/30/19           0
     3       Ken     4/15/19           1

The cases for Group_ID 2 and 3 are the roadblock. The data is already sorted by date.

Thank you for any help in advance

Richard Richard · Accepted Answer · 2020-10-27T21:33:53

Use DOW processing to determine where the last name change was. Apply that information in a succeeding loop.

Example:

data want;
  do _n_ = 1 by 1 until (last.id);
    set have;
    by id name notsorted;
    if first.name then _index_of_last_name_change = _n_;
  end;

  do _n_ = 1 to _n_;
    set have;
    if _index_of_last_name_change <= _n_ <= _index_of_last_name_change+1 then OUTPUT;
  end;
  drop _:;
run;

Last Changed row +1 in a group

1 Answers