Get future date from an existing date with conditionally adding months or years to the existing date

[KranthiK_J]

Hi

I am working on a sample data set below and need to get the missing end_dt column from the start_dt,asked_term,renew_term

How to caluclate:

Iteration 1: Start_dt + renew_term 

If iteration 1 result is greater than today() then end as END_DT

ELSE 

Add asked_term in a loop until  the output date is greater than today()

Calculation sample:

Dataset:

start_dt	       end_dt	   asked_term	                     renew_term                      
16DEC2005:00:00:00	.	   3 year                        	1 year                        
14JUN2021:00:00:00	.	   1 year                        	1 year                                                                      
29AUG2019:00:00:00	.	   2 year                        	1 year                                               
12DEC2013:00:00:00	.	   Monthly                       	1 year                                              
10JAN2019:00:00:00	.	   3 year                        	4 year                        

[ballardw]

Your picture shows date values, not that I understand what you are trying to show with the picture.

Your "dataset' has datetime value. Which is it? Date or Datetime? If all of the times are 00:00:00 why bother with time?

What variable in your data holds 'iteration'?

I think you need to show us what the expected result of this operation looks like as I am pretty sure I don't understand what your are asking for.

I might make some guesses for something like 3 years but "monthly" doesn't have any number of times associated with it. Does that mean to create dates (or datetimes) until you exceed the values that SAS wants to handle? Note that for dates that would mean around year 20,000 not than any of the date formats will display past year 9999...

[KranthiK_J]

The values i have in the data set is date time, we need to convert the datetime to date before we do our calculation. As you mentioned, they are all 00:00:00 which is why we dont need the time just convert the datetime to date and then do our calculation.

There is no variable that holds the iteration, i was just giving an example how i need the calculation to be done to get the end_date. 

And the monthly is just one month. Instead of 3 years or 1 year from the second iteration we have to add 1 month everytime until the end_date is greater than today.

[Tom · Accepted solution]

So convert your dates into dates and convert your interval into two variables. One with the number and one with the type of interval.

Example:

data have;
  input start_dt :date. interval_value interval_type $ ;
  format start_dt date9.;
cards;
16DEC2005 3 year
14JUN2021 1 year
29AUG2019 2 year
12DEC2013 1 month
10JAN2019 3 year
;

Sounds like you want to increment until the end date is on or after today.

data want;
  set have;
  do offset=0 by interval_value until(end_dt >= today());
    end_dt=intnx(interval_type,start_dt,offset,'same');
  end;
  format end_dt date9.;
run;

Result

                    interval_    interval_
Obs     start_dt      value        type       offset       end_dt

 1     16DEC2005        3          year          18     16DEC2023
 2     14JUN2021        1          year           2     14JUN2023
 3     29AUG2019        2          year           4     29AUG2023
 4     12DEC2013        1          month        113     12MAY2023
 5     10JAN2019        3          year           6     10JAN2025

[KranthiK_J]

Thanks for the solution tom. However, here you are just considering the 'asked_term'  as the interval value and interval type. 

But what i need is first add the 'Renew_term' only once. When added, if the END_DATE doesnt increment to more than today, then only i need to consider the 'Asked_term' and offset the same value until i get the desired date (which is more than today).

Here when just considered the asked_term the end_date would not be right for all the cases.

[KranthiK_J]

consider the below example:
Start_dt:10MAR2023:00:00:00 Asked_Term: 3 year Renew_term: 1 year

In this case the expected result is End_dt: 10MAR2024

Cause always first add renew_term once if the condition didn't meet then go to the asked term and add the asked_term value until it meets the condition

[Tom]

---

@KranthiK_J wrote:

Thanks for the solution tom. However, here you are just considering the 'asked_term'  as the interval value and interval type. 

But what i need is first add the 'Renew_term' only once. When added, if the END_DATE doesnt increment to more than today, then only i need to consider the 'Asked_term' and offset the same value until i get the desired date (which is more than today).

Here when just considered the asked_term the end_date would not be right for all the cases.

---

Try it yourself.  

Sketch:

1. Use the "renew term" variables to derive END from START.
2. Change the DO loop to use WHILE() instead of UNTIL() so it is possible it runs zero times.

If it does not work then show what you did and explain how it did not do what you want.