I can either do this in Base SAS or SAS DI Studio or a combination of both.
I have a table with contract information. So contract ID, contract period , begin date and end date. Normally each contact ID/contract period has a date span of one year. So January 1st through December 31st.
That table can be joined to a header table on contract id and contact period. In the header table there is an action date, then within each action date there can be multiple rows with begin and end dates. For example:
Action Date Begin End
Mar 2015 Jan 1 , 2015 may 31, 2015
June 1, 2015 November 30, 2015
Feb 2015 Jan 1 , 2015 may 31, 2015
June 1, 2015 November 30, 2015
Dec 1, 2015 Dec 31, 2015
I need to start with the most recent action date which is March 2015 and looks at the begin/end dates associated with that action date to complete the date range for the contract period. In this case for March 2015 action date the 2 rows only cover 11 months out of 12 for that contract id and period. In this case I need to look at the action date before to see if I can find that missing month.
I would like to see the following for Action date of March 2015.
Action Date Begin End
Mar 2015 Jan 1 , 2015 may 31, 2015
June 1, 2015 November 30, 2015
Dec 1, 2015 Dec 31, 2015
Since I need to keep the history for all action dates I then need to forget about March 2015 action date and focus on Feb 2015 action date. Since the 3 rows associate with that action date cover the whole contract I am done with that date.
If there was another action date I need to do the same process as I did for the first action date by trying to complete the contract period and if those dates arent present I have to look back to the previous action date and even one before that if not available. This process continues until all actions dates show a true history.
I am not asking for the code to be written - I am really looking for some ideas on where to even begin.
Click image to register for webinar
