Date Ranges and history

Kam — Wed, 28 Feb 2018 12:41:06 GMT

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.

Re: Date Ranges and history

Kurt_Bremser — Wed, 28 Feb 2018 13:08:25 GMT

Please provide some examples for your datasets. Use the macro provided in https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 to convert your datasets to data steps, and post the resulting code as advised in https://communities.sas.com/t5/help/faqpage/faq-category-id/posting?nobounce.

topic Re: Date Ranges and history in SAS Programming

Date Ranges and history

Re: Date Ranges and history