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
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
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...
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.
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
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 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:
If it does not work then show what you did and explain how it did not do what you want.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.