Join overlapping date ranges?

Question

I need to join table A and table B to create table C. Table A and Table B store status flags for the IDs. The status flags (A_Flag and B_Flag) can change from time to time, so one ID can contain multiple rows, which represents the history of the ID's statuses. The flags for a particular ID can change independently of each other, which can result in one row in Table A belonging to multiple rows in Table B, and vice versa. The resulting table (Table C) needs to be a list of unique date ranges covering every date within the IDs life (01/01/2008-18/08/2008), and A_Flag and B_Flag values for each date range. The actual tables contain hundreds of IDs with each ID having a varying numbers of rows per table. I have access to SQL and SAS tools to achieve the end result. Source - Table A ID Start End A_Flag 1 01/01/2008 23/03/2008 1 1 23/03/2008 15/06/2008 0 1 15/06/2008 18/08/2008 1 Source - Table B ID Start End B_Flag 1 19/01/2008 17/02/2008 1 1 17/02/2008 15/06/2008 0 1 15/06/2008 18/08/2008 1 Result - Table C ID Start End A_Flag B_Flag 1 01/01/2008 19/01/2008 1 0 1 19/01/2008 17/02/2008 1 1 1 17/02/2008 23/03/2008 1 0 1 23/03/2008 15/06/2008 0 0 1 15/06/2008 18/08/2008 1 1

Answer 1

Here is a solution. I added additional test data to make sure the logic worked for different ID values.

/* Table A */

data table_a;

informat Start End ddmmyy10.;

input ID $ Start End A_Flag;

format Start End ddmmyy10.;

datalines;

1 01/01/2008 23/03/2008 1

1 23/03/2008 15/06/2008 0

1 15/06/2008 18/08/2008 1

2 01/11/2009 15/12/2009 1

;

/* Table B */

data table_b;

informat Start End ddmmyy10.;

input ID $ Start End B_Flag;

format Start End ddmmyy10.;

datalines;

1 19/01/2008 17/02/2008 1

1 17/02/2008 15/06/2008 0

1 15/06/2008 18/08/2008 1

2 28/10/2009 08/11/2009 1

2 08/11/2009 20/12/2009 0

;

proc sort data=table_a; by ID Start End; run;

proc sort data=table_b; by ID Start End; run;

/* save all dates (starting or ending) for each ID */

data datestacka(keep=ID date);

set table_a table_b;

format date ddmmyy10.;

date=Start; output datestacka;

date=End; output datestacka;

run;

proc sort nodupkey data=datestacka; by ID date; run;

/* create every start/end date range for every ID */

data datestackb(drop=date);

set datestacka;

by ID;

format Start End ddmmyy10.;

End=date;

Start=lag(date);

if not first.ID then output;

run;

/* read through the entire date range file.

For each record in the date range file,

scan thru both Table A and Table B */

data final;

set datestackb;

/* save the A_Flag for a date range within table A that's also in the date range file */

ahit=0;

A_Flag=0;

do apoint=1 to anobs;

set table_a(rename=(id=aid start=astart end=aend A_Flag=flag))

point=apoint nobs=anobs;

format astart aend ddmmyy10.;

if ahit=0 and id=aid and start >= astart and end <= aend then do;

A_Flag=flag;

ahit=1;

end;

end;

/* save the B_Flag for a date range within table B that's also in the date range file */

bhit=0;

B_Flag=0;

do bpoint=1 to bnobs;

set table_b(rename=(id=bid start=bstart end=bend B_Flag=flag))

point=bpoint nobs=bnobs;

format bstart bend ddmmyy10.;

if bhit=0 and id=bid and start >= bstart and end <= bend then do;

B_Flag=flag;

bhit=1;

end;

end;

if ahit or bhit then output;

drop ahit aid astart aend bhit bid bstart bend flag;

run;