DATA Step, Macro, Functions and more

Date Ranges and history

Reply
New Contributor Kam
New Contributor
Posts: 2

Date Ranges and history

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. 

Super User
Posts: 9,874

Re: Date Ranges and history

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-dat... 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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 1 reply
  • 91 views
  • 0 likes
  • 2 in conversation