For each ID, this program maintains a hash object (i.e. a dynamic lookup table) with an entry for each condition encountered. Each entry will have the most recent FINISH value and the corresponding sequence number.
As new observations are read in, you can see if there is a prior hash dataitem (i.e. hash "row") for the same condition. If not set the sequence to 1. But if yes, compare the current start to the most recent finish for that condition to determine whether to increment the sequence. I.e. the sequence is always up to date for each condition.
Then just build the new client_condition_grp from the client id, condition, and sequence:
data have;
input client:$ eventid:$ condition:$ start:date9. finish:date9. ;
format start DATE9. finish DATE9. ;
datalines ;
CL01 EV01 CN1 01AUG2019 07AUG2019
CL01 EV02 CN1 10AUG2019 18AUG2019
CL01 EV03 CN1 27AUG2019 30AUG2019
CL02 EV04 CN1 01AUG2019 07AUG2019
CL02 EV05 CN2 10AUG2019 18AUG2019
CL02 EV06 CN2 27AUG2019 30AUG2019
run;
data want (drop=_:);
set have;
by client;
if _n_=1 then do;
call missing(_prior_finish,_sequence);
declare hash h ();
h.definekey('condition');
h.definedata('condition','_prior_finish','_sequence');
h.definedone();
end;
if first.client then h.clear() /*Blank slate at the start of each client */;
if h.find()^=0 then _sequence=1 /*If this condition not yet in the hash */;
else if start-7 > _prior_finish then _sequence=_sequence+1;
_prior_finish=finish;
h.replace() /*Update the dataitem in the hash object */;
length client_condition_group $9 ;
client_condition_group=cats(client,condition,'_',_sequence);
run;
This program assumes data are sorted by START within CLIENT.
... View more
