Aggregating multiple observations depending on validity ranges

Question

I am facing a problem regarding the concatenation of multiple observations depending on validity ranges. The function I am trying to reproduce is similar to the Listagg() function in Oracle but I want to use it with regards to validity ranges.

Here is a reproducible minimal dataset:

data have;
infile datalines4 delimiter=",";
input id var $ value $ start:datetime20. end:datetime20.;
format start end datetime20.;
datalines4;
1,NAME,AAA,01JAN2014:00:00:00,31DEC2020:00:00:00
1,MEMBER,Y,01JAN2014:00:00:00,31DEC9999:00:00:00
2,NAME,BBB,01JAN2014:00:00:00,31DEC9999:00:00:00
2,MEMBER,Y,01JAN2014:00:00:00,31DEC2016:00:00:00
2,MEMBER,N,01JAN2017:00:00:00,31DEC2019:00:00:00
3,NAME,CCC,01JAN2014:00:00:00,31DEC9999:00:00:00
3,MEMBER,Y,01JAN2014:00:00:00,31DEC2017:00:00:00
3,MEMBER,N,01JAN2014:00:00:00,31DEC2017:00:00:00
4,NAME,DDD,01JAN2014:00:00:00,31DEC9999:00:00:00
4,MEMBER,Y,01JAN2014:00:00:00,31DEC2017:00:00:00
4,MEMBER,N,10JAN2016:00:00:00,31DEC2019:00:00:00
5,NAME,EEE,01JAN2014:00:00:00,31DEC9999:00:00:00
5,MEMBER,Y,01JAN2014:00:00:00,31DEC2017:00:00:00
5,MEMBER,N,01JAN2014:00:00:00,31DEC2017:00:00:00
5,MEMBER,Y,01JAN2019:00:00:00,31DEC2019:00:00:00
5,MEMBER,N,01JAN2019:00:00:00,31DEC2019:00:00:00
;;;;
run;

                            

What I would like to do is to concatenate the value variable for each group of var inside an id. However, there a multiple types of cases:

• If there is only one value for a given var inside an id, don't do anything (e.g. case of id=1 in my example)
• If validity ranges are consecutive, output every value of var inside an id (e.g. case of id=2)
• If validity ranges are the same for the same var inside an id, concatenate them altogether (e.g. case of id=3)
• If validity ranges are overlapping, the range that shares the two value of var are concatenated with the corresponding validity range (e.g. case of id=4)
• If there are multiple validity ranges that are non consecutive for the same value in var inside an id, concatenate each that shares the same validity ranges (e.g. case of id=5)

Here is the desired result:

                            

Following @Kiran's answer on how to do Listagg function in SAS and @Joe's answer on List Aggregation and Group Concatenation in SAS Proc SQL, I tried to use the CATX function.

This is my attempt:

proc sort data=have;
by id var start;
run;

data staging1;
    set have;
    by id var start;
    if first.var then group_number+1;
run;

/* Simulate LEAD() function in SAS */
data staging2;
    merge staging1 staging1(firstobs = 2 
                    keep=group_number start end
                    rename=(start=lead_start end=lead_end group_number=nextgrp));
    if group_number ne nextgrp then do;
        lead_start = .;
        lead_end = .;
    end;
    drop nextgrp;

    format lag_: datetime20.;
run; 

proc sort data=staging2;
by id var group_number start;
run;

data want;
    retain _temp;
    set staging2;
    by id var group_number;

    /* Only one obs for a given variable, output directly */
    if first.group_number = 1 and last.group_number = 1 then
        output;
    else if first.group_number = 1 and last.group_number = 0 then
        do;
            if lead_start ne . and lead_end ne . 
                and ((lead_start < end) or (lead_end < start)) then
                do;
                    if (lead_start = start) or (lead_end = end) then
                        do;
                            retain _temp;
                            _temp = value;
                        end;

                    if (lead_start ne start) or (lead_end ne end) then
                        do;
                            _temp = value;
                            end = intnx('dtday',lead_start,-1);
                            output;
                        end;
                end;
            else if lead_start ne . and lead_end ne . and intnx('dtday', end, 1) = lead_start then
                do;
                    _temp = value;
                    output;
                end;
            else output;
        end;
    else if first.group_number = 0 and last.group_number = 1 then
        do;
                    /* Concatenate preceded retained value */
                    value = catx(";",_temp, value);
                    output;
                    call missing(_temp);
        end;
    else output;

    drop _temp lead_start lead_end group_number;
run;

My attempt did not solve all the problems. Only the cases of id=1 and id=3 were correctly output. I am starting to think that the use of first. and last. as well as the simulated LEAD() function might not be the most optimal one and that there is a probably a better way to do this.

Result of my attempt:

                            

Desired results in data:

data want;
infile datalines4 delimiter=",";
input id var $ value $ start:datetime20. end:datetime20.;
format start end datetime20.;
datalines4;
1,NAME,AAA,01JAN2014:00:00:00,31DEC2020:00:00:00
1,MEMBER,Y,01JAN2014:00:00:00,31DEC9999:00:00:00
2,NAME,BBB,01JAN2014:00:00:00,31DEC9999:00:00:00
2,MEMBER,Y,01JAN2014:00:00:00,31DEC2016:00:00:00
2,MEMBER,N,01JAN2017:00:00:00,31DEC2019:00:00:00
3,NAME,CCC,01JAN2014:00:00:00,31DEC9999:00:00:00
3,MEMBER,Y;N,01JAN2014:00:00:00,31DEC2017:00:00:00
4,NAME,DDD,01JAN2014:00:00:00,31DEC9999:00:00:00
4,MEMBER,Y,01JAN2014:00:00:00,09JAN2016:00:00:00
4,MEMBER,Y;N,10JAN2016:00:00:00,31DEC2017:00:00:00
4,MEMBER,N,01JAN2018:00:00:00,31DEC2019:00:00:00
5,NAME,EEE,01JAN2014:00:00:00,31DEC9999:00:00:00
5,MEMBER,Y;N,01JAN2014:00:00:00,31DEC2017:00:00:00
5,MEMBER,Y;N,01JAN2019:00:00:00,31DEC2019:00:00:00
;;;;
run;

Answer 1

It's pretty hard to do this in raw SQL, without built in windowing functions; data step SAS will have some better solutions.

Some of this depends on your data size. One example, below, does exactly what you ask for, but it probably will be impractical with your real data. Some of that is the 31DEC9999 dates - that makes for a lot of data - but even without that, this has thousands of rows per person, so if you have a million people or something this will get rather large. But, it might still be the best solution, depending on what you need - it does give you the absolute best control.

* First, expand the dataset to one row per day/value.  (Hopefully you do not need the datetime - just the date.)
data daily;
  set have;
  do datevar = datepart(start) to datepart(end);
    output;
  end;
  format datevar date9.;
  drop start end;
run;

  
proc sort data=daily;
  by id var datevar value;
run;

*Now, merge together the rows to one row per day - so days with multiple values will get merged into one.;
data merging;
  set daily;
  by id var datevar;
  retain merge_value;
  if (first.datevar and last.datevar) then output;
  else do;
    if first.datevar then merge_value = value;
    else merge_value = catx(',',merge_value,value);
    if last.datevar then do;
        value = merge_value;
        output;
    end;
  end;
  keep id var datevar value;
run;

proc sort data=merging;
by id var value datevar;
run;


*Now, re-condense;
data want;
  set merging;
  by id var value datevar;
  retain start end;
  
  last_datevar = lag(datevar);
    
  if first.value then do;
    start = datevar;
    end = .;
  end;
    
  else if last_datevar ne (datevar - 1) then do;
    end = last_datevar;
    output;
    start = datevar;
    end = .;
  end;
  
  if last.value then do;
    end = datevar;
    output;
  end;
  
  format start end date9.;
run;

I do not necessarily recommend doing this - it's provided for completeness, and in case it turns out it's the only way to do what you do.

Easier, most likely, is to condense using the data step using an event level dataset, where 'start' and 'end' are events. Here's an example that does what you require; it translates the original dataset to only 2 rows per original row, and then uses logic to decide what should happen for each event. This is pretty messy, so you'd want to clean it up for production, but the idea should work.

* First, make event level dataset so we can process the start and end separately;
data events;
  set have;
  type = 'Start';
  dt_event = start;
  output;
  type = 'End';
  dt_event = end;
  output;
  drop start end;
  format dt_event datetime.;
run;

proc sort data=events;
  by id var dt_event value;
run;

*Now, for each event, a different action is taken.  Starts and Ends have different implications, and do different things based on those.;
data want;
  set events(rename=value=in_value);
  by id var dt_event;
  retain start end value orig_value;
  format value new_value $8.;

  * First row per var is easy, just start it off with a START;
  if first.var then do;
    start = dt_event;
    value = in_value;
  end;     
  else do; *Now is the harder part;
    * For ENDs, we want to remove the current VALUE from the concatenated VALUE string, always, and then if it is the last row for that dt_event, we want to output a new record;
    if type='End' then do;
    
        *remove the current (in_)value;
        if first.dt_event then orig_value = value;
        do _i = 1 to countw(value,',');
            if scan(orig_value,_i,',') ne in_value then new_value = catx(',',new_value,scan(orig_value,_i,','));
        end;
        orig_value = new_value;
 
        if last.dt_event then do;
            end = dt_event;
            output;
            start = dt_event + 86400;
            value = new_value;
            orig_value = ' ';
        end;
    end;
    else do;
        * For START, we want to be more careful about outputting, as this will output lots of unwanted rows if we do not take care;
        end = dt_event - 86400;
        if start < end and not missing(value) then output;
        value = catx(',',value,in_value);
        start = dt_event;
        end = .;
    end;
  end;

  format start end datetime21.;
  keep id var value start end;
run;

Last, I'll leave you with this: you probably work in insurance, pharma, or banking, and either way this is a VERY solved problem - it's done a lot (this sort of windowing). You shouldn't really be writing new code here for the most part - first look in your company, and then if not, look for papers in either PharmaSUG or FinSUG or one of the other SAS user groups, where they talk about this. There's probably several dozen implementations of code that does this already published.