I have a variable in SAS which represents the last repricing of an asset, a subset looks like:
Data Subset;
input ID Date_of_Last_Repricing Repricing_Frequency;
datalines;
1 2011 2
2 2013 2
3 2016 3
4 2017 1
5 2015 1
6 2015 1
7 2017 2
8 2017 2
9 2011 1
10 2010 1
11 2010 1
12 2011 2
13 2012 3
;
run;
I'm looking to create a loop that increases the variable date_of_last_repricing by the repricing frequency until it gets as close to 2017 as possible. For example the data set would look like:
Data want;
input ID Date_of_Last_Repricing Repricing_Frequency Year;
datalines;
1 2011 2 2017
2 2013 2 2017
3 2016 3 2016
4 2017 1 2017
5 2015 1 2017
6 2015 1 2017
7 2017 2 2017
8 2017 2 2017
9 2011 1 2017
10 2010 1 2017
11 2010 3 2016
12 2011 2 2017
13 2012 3 2015
;
run;
So far the code I've tried to use is
data testing;
set test;
if dateoflastrepricing+repricing_schedule<2017
then do until sum(dateoflastrepricing+repricing_schedule)>2017;
sum(dateoflastrepricing+repricingschedule);
end;
run;
No loop or accumulator needed:
year = Date_of_Last_Repricing + ( floor((2017-Date_of_Last_Repricing)/Repricing_Frequency) * Repricing_Frequency );
No loop or accumulator needed:
year = Date_of_Last_Repricing + ( floor((2017-Date_of_Last_Repricing)/Repricing_Frequency) * Repricing_Frequency );
nice ,ty.
Based on the final observation in your data set, it looks like YEAR should never exceed 2017. For that result:
data want;
set have;
year = date_of_last_repricing;
do k=1 to 100 while (year + repricing_frequency <= 2017);
year = year + repricing_frequency;
end;
drop k;
run;
The purpose of the 1 to 100 loop is to prevent an infinite loop for bad data, such as one of the key variables having a missing value.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.