Hello all, So I have a dataset of clients with one or more social service episodes and within each episode there can be one or more events. Each event has a begin date and an end date. I want to make a wide dataset so that there is a column for each day for a given time period, for example, calendar year 2019. And for each event I want to flag the days that occur during the given time period. There is more that I want to do but this would be a great start.   Here is an example data set:   data have; infile datalines; input Client_ID $ Episode_ID $ Event_ID $ Begin_Date :date9. End_Date :date9. Event_Plcmnt $; format Begin_date End_Date date9.; datalines; 001 001 001 20MAY2019 23MAY2019 GC 001 001 002 22MAY2019 23MAY2019 OTHER 001 001 003 23MAY2019 23OCT2019 GC 001 001 004 29JUN2019 30JUN2019 OTHER 001 001 006 14JUL2019 15JUL2019 OTHER 001 001 007 19JUL2019 23JUL2019 OTHER 001 001 005 25JUL2019 08AUG2019 OTHER 001 001 008 09AUG2019 10AUG2019 OTHER 001 001 009 11AUG2019 12AUG2019 OTHER 001 002 010 01NOV2019 16APR2020 URC And here is what I want it to look like: Client_ID Episode_ID Event_ID Begin_Date End_Date Event_Plcmnt 01JAN2019 02JAN2019 ... 20MAY2019 21MAY2019 22MAY2019 23MAY2019 24MAY2019 25MAY2019 ... 30DEC2019 31DEC2019 001 001 001 20May2019 23May2019 GC     ... 1 1 1 1     ...     001 001 002 22May2019 23May2019 Other     ...     1 1     ...     001 001 003 23May2019 23Oct2019 GC     ...       1 1 1 ...     001 001 004 29Jun2019 30Jun2019 Other     ...             ...     001 001 005 14Jul2019 15Jul2019 Other     ...             ...     001 001 006 19Jul2019 23Jul2019 Other     ...             ...     001 001 007 25Jul2019 8Aug2019 Other     ...             ...     001 001 008 09Aug2019 10Aug2019 Other     ...             ...     001 001 009 11Aug2019 12Aug2019 Other     ...             ...     001 002 010 01Nov2019 16Apr2020 URC     ...             ... 1 1 I've truncated, of course, to save space.  Also, there are a number of other columns that I did not include in the sample dataset that I would need to carry over.    Let me know what you all think.   Best,   Marc   ... View more

Hi all, So I have a new problem to solve with the data I've been working on.  I am looking at clients that can have multiple episodes and each episode can have multiple events.  I want to make it so that the dates on concurrent events have no gaps and do not overlap.    Here is what  a simplified version of the data looks like: Client_ID Episode_ID Event_ID Begin_Date End_Date Event_Plcmnt 1 1 1 20-May-19 23-May-19 GC    1 1 2 22-May-19 23-May-19 OTHER 1 1 3 23-May-19 23-Oct-19 GC    1 1 4 29-Jun-19 30-Jun-19 OTHER 1 1 6 14-Jul-19 15-Jul-19 OTHER 1 1 7 19-Jul-19 23-Jul-19 OTHER 1 1 5 25-Jul-19 8-Aug-19 OTHER 1 1 8 9-Aug-19 10-Aug-19 OTHER 1 1 9 11-Aug-19 12-Aug-19 OTHER 1 2 10 1-Nov-19 16-Apr-20 URC   2 3 11 19-Feb-16 1-Mar-16 LRC 2 3 12 5-Mar-16 14-Jun-16 URC     Here is what I want it to look like after: Client_ID Episode_ID Event_ID Begin_Date End_Date Event_Plcmnt 1 1 1 20-May-19 22-May-19 GC    1 1 2 22-May-19 23-May-19 OTHER 1 1 3 23-May-19 29-Jun-19 GC    1 1 4 29-Jun-19 30-Jun-19 OTHER 1 1 3 30-Jun-19 14-Jul-19 GC    1 1 6 14-Jul-19 15-Jul-19 OTHER 1 1 3 15-Jul-19 19-Jul-19 GC 1 1 7 19-Jul-19 23-Jul-19 OTHER 1 1 3 23-Jul-19 25-Jul-19 GC 1 1 5 25-Jul-19 8-Aug-19 OTHER 1 1 3 8-Aug-19 9-Aug-19 GC 1 1 8 9-Aug-19 10-Aug-19 OTHER 1 1 3 10-Aug-19 11-Aug-19 GC 1 1 9 11-Aug-19 12-Aug-19 OTHER 1 1 3 12-Aug-19 23-Oct-19 GC 1 2 10 1-Nov-19 16-Apr-20 URC   2 3 11 19-Feb-16 5-Mar-16 LRC 2 3 12 5-Mar-16 14-Jun-16 URC     I've highlighted the new rows and changes to existing rows green. The rules I am using:   1) If two events that are in the same episode that overlap the end_date of the older episode becomes the begin_date of the newer episode (see episode 3).  2) If there is a gap days between events in the same episode the end_date of the older episode becomes the begin_date of the newer episode (see episode 11). 3) If the dates of an event are encompassed by another event in the same episode then the end_date of the encompassing episode becomes the begin_date of the encompassed episode and a new row is created with the Event_ID and  the Event_Plcmnt of the compassing episode that has the begin_date of the encompassed episode and either the end_date of the original encompassing event or, if there is more than one encompassed event, the begin_date of the next encompassed event.   Some notes about the data: There are a lot more fields than what I have here...I wanted to keep it as simple as possible. I found that the Event_IDs are not in chronological order.  Sometimes a newer ID will have a lower number. Here is the data in a usable form:   data have; infile datalines truncover; input Client_ID $ Episode_ID $ Event_ID $ Begin_Date End_Date Event_Plcmnt; datalines; 001 001 001 20MAY2019 23MAY2019 GC 001 001 002 22MAY2019 23MAY2019 OTHER 001 001 003 23MAY2019 23OCT2019 GC 001 001 004 29JUN2019 30JUN2019 OTHER 001 001 006 14JUL2019 15JUL2019 OTHER 001 001 007 19JUL2019 23JUL2019 OTHER 001 001 005 25JUL2019 08AUG2019 OTHER 001 001 008 09AUG2019 10AUG2019 OTHER 001 001 009 11AUG2019 12AUG2019 OTHER 001 002 010 01NOV2019 16APR2020 URC 002 003 011 19FEB2016 01MAR2016 LRC 002 003 012 05MAR2016 14JUN2016 URC ;   Hopefully this is clear enough.  I tried using the lag function but there can sometimes be 10 or more event nested inside another it got too complex to keep track of.   Best, Marc ... View more