first. and last. with multiple occurences

Question

I am trying to gather dates from a dataset using the first.variable and last.variable from SAS. Here is the code to create a reproducible example:

data example;
infile datalines delimiter = ",";
input id $ code $ valid_from valid_to;
format valid_from IS8601DA10. valid_to IS8601DA10.;
datalines;
1A,ABC,20058,20177
1A,DEF,20178,20481
1A,DEF,20482,20605
1A,DEF,20606,21548
1A,DEF,21549,21638
1A,DEF,21639,21729
1A,ABC,21730,21733
1A,ABC,21734,21808
1B,MNO,20200,20259
1B,PQR,20260,20269
1B,STU,20270,20331
1B,VWX,20332,20361
1B,VWX,20362,22108
1B,VWX,22109,22164
1B,VWX,22165,22165
1B,VWX,22166,2936547
;
run;

The idea is to get, for each id, only one observation per code with the corresponding range of dates it cover.

Here is my code:

proc sort data=example out=example_sorted; by code valid_from; run;

data collapse_val_dates; 
set example_sorted;
by code valid_from;

if first.code = 1 and last.code = 1 then do;
    output;
end;
if first.code = 1 and last.code = 0 then do;
    hold = valid_from;
    retain hold;
    end;
if first.code = 0 and last.code = 1 then do;
        valid_from = hold;
        output;
    end;

drop hold;
run;

Here is the result (table collapse_val_dates):

+----+------+------------+------------+
| id | code | valid_from |  valid_to  |
+----+------+------------+------------+
| 1A | ABC  | 2014-12-01 | 2019-09-16 |
| 1A | DEF  | 2015-03-31 | 2019-06-29 |
| 1B | MNO  | 2015-04-22 | 2015-06-20 |
| 1B | PQR  | 2015-06-21 | 2015-06-30 |
| 1B | STU  | 2015-07-01 | 2015-08-31 |
| 1B | VWX  | 2015-09-01 | 9999-12-31 |
+----+------+------------+------------+

It produces what I expect for id=1B but not for id=1A. Indeed, as the code=ABC appears once in the beginning and twice at the end, the result table put valid_from=2014-12-01.

What I would like is the valid_from for code=ABC to be 2019-06-30. In other words, I would like SAS to "forget" the first occurence of the code if there is an (or multiple) other code in between. The final table would look like this:

+----+------+------------+------------+
| id | code | valid_from |  valid_to  |
+----+------+------------+------------+
| 1A | DEF  | 2015-03-31 | 2019-06-29 |
| 1A | ABC  | 2019-06-30 | 2019-09-16 |
| 1B | MNO  | 2015-04-22 | 2015-06-20 |
| 1B | PQR  | 2015-06-21 | 2015-06-30 |
| 1B | STU  | 2015-07-01 | 2015-08-31 |
| 1B | VWX  | 2015-09-01 | 9999-12-31 |
+----+------+------------+------------+

Answer 1

For a single pass over the data you can't output a date range for a code while serially processing a group because later rows in the group could overwrite the date range wanted.

You will need to either

• code multiple steps, or
• perform a single pass and use temporary storage

Presume have is sorted by id valid_from and that valid_to never overlaps a succeeding valid_from within the id group.

Multiple Steps

Compute a group number for rows grouped by contiguous code for use in final ordering.

* multi step way;

data stage1;
  set have;
  by id code notsorted;
  if first.code then group_number+1;
run;

proc sort data=stage1 out=stage2;
  by id code group_number valid_from;
run;

* remember there can be multiple contiguous code groups within id & group;

data stage3;
  do until (last.code);
    set stage2;
    by id code group_number;
    if first.group_number then _start = valid_from;
    if last.code then do;
      valid_from = _start;
      OUTPUT;                /* date range for contiguous code group */
    end;
  end;
  drop _start;
run;

proc sort data=stage3 out=want(drop=group_number);
  by id valid_from;
run;

Single Pass

A DOW loop (a loop that has a SET statement within it) can compute a result over a group and subgroup and output one row per combination. Temporary storage can be a hash (for an arbitrary number of subgroups), or an array for an assumed maximum number of subgroups.

Example:

A temporary array of fixed size 1,000 is used to store temporary data that should be modified while examining the group.

* find the range of the dates from the last set of contiguous rows of a code within id;

data want(keep=id code valid_:);
  array dates (1000,2)   8 _temporary_; /* ,1 for _from and ,2 for _to */
  array codes (1000)   $50 _temporary_;
  array seq   (1000)     8 _temporary_; /* sequence for output order */

  * process the id group;
  do _n_ = 1 by 1 until (last.id);
    set have;
    by id code notsorted;

    * save start of date range in temporay storage;
    if first.code then do;

      * linear search for slot to use for subgroup code;
      do _index = 1 by 1 
        until (missing(codes(_index)) or codes(_index)=code);
      end;

      codes(_index) = code;
      dates(_index,1) = valid_from;
      seq  (_index) = _n_ + _index / 1000; * encode order value with lookup index;
    end;

    * save end of date range;
    if last.code then 
      dates(_index,2) = valid_to;
  end;

  *---;

  * process each code within id;

  call sort (of seq(*)); * order of first date for last code subgroup;

  do _index = 1 to dim(seq);
    if missing(seq(_index)) then continue;

    * extract encoded information;
    _ix = round((seq(_index) - int(seq(_index))) * 1000);

    code = codes(_ix);
    valid_from = dates(_ix,1);
    valid_to   = dates(_ix,2);

    OUTPUT;
  end;

  * clear out temporary arrays for next group processing;
  call missing (of dates(*), of codes(*), of seq(*));
run;