Asked • 04/27/19

Join overlapping date ranges?

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

1 Expert Answer

By:

Rick A. answered • 04/28/19

Tutor
5 (33)

Experienced SAS Professional

Still looking for help? Get the right answer, fast.

Ask a question for free

Get a free answer to a quick problem.
Most questions answered within 4 hours.

OR

Find an Online Tutor Now

Choose an expert and meet online. No packages or subscriptions, pay only for the time you need.