I currently have data regarding income streams over four years, the income stream fills a 4x4 matrix as follows:
Year1 Year2 Year3 Year4
2017 10 15 20 25
2018 10 15 20 25
2019 10 15 20 25
2020 10 15 20 25
The income streams come from a debt instrument where the rows indicate when the debt was issued and the columns indicate the flow of income coming in per year.
I am trying to build another matrix to highlight whether or not the debt instrument was priced that year to create this matrix:
Year1 Year2 Year3 Year4
2017 1 0 0 0
2018 0 1 0 0
2019 0 0 1 0
2020 0 0 0 1
This is a very simple example and it goes on to work on how often the instrument is repriced etc. for simplicity, is there a way to create an if or where function that returns a 1 or 0 depending on whether the year is equal to or within a certain amount of time of the instrument being issued.
So far i'm thinking along the lines
%Repricing = 1data want;
set have;
if Year[i] <= &Repricing;
then Year[i]=1; run;
It's probably obvious that SAS isn't my language of choice, TIA.
I guess another way to look at this would be to allocate:
*repricing is in months;
%Repricing = 24;
*translate months to year;
data want;
set have;
if column_header ^=1
then return 1;
else if column_header <1 or >1
then return 0;
run;
is there a way to create an if or where function that returns a 1 or 0 depending on whether the year is equal to or within a certain amount of time of the instrument being issued.
I have no idea what that means or how it works as a decision rule.
I'll create a false scenario, imagine it is 31/12/2016 so
%let date=31/12/2016;
imagine that this debt instrument is repriced only when it is issued (fixed asset)
the table displays
Row(1-4): when the debt instrument expires
Column (1-4): the payments made by the debt instrument.
Every time the debt instrument expires, it has to be replaced immediately and is re-priced (A 1 is flagged)
so i'm looking to make a dataset where you check whether or not the debt instrument is expiring and I wish to flag a 1.
So example a debt instrument expires in 2017 , so flag 2017,2017 row,column
expire 2018 - flag 18,18
this case means a diagonal matrix of 1's and 0 everywhere else
So far this code has worked for the example but it could get difficult for maturity / repricing events other than just 1 year.
data want;
set have;
*arrays for years and flags;
array _year(2017:2020) year2017-year2020;
array _flag(2017:2020) flag2017-flag2020;
*loop over array;
do i=2017 to hbound(_year);
/*check if year matches year in variable name*/
if put(yearD, 4.) = compress(vname(_year(i)),, 'kd')
then _flag(i)=1;
else _flag(i)=0;
end;
drop i;
run;
it could get difficult for maturity / repricing events other than just 1 year.
How do we know when a debt instrument is going to expire?
In your original example, Year one is 2018. Year 2 is 2019, etc. 17's expire in 18. 18's expire in 19. How do we know that other than just knowing?
I guess the only way to really know is by introducing a macro variable
%Repricingperiod = 'Insert Years or Months Here';
I'm starting to think an interactive long macro in this sort of idea
%let initialyear= 'insert initial year here';
%let repricingfrequency= 'Insert repricing in years here';
If we make the first column ID = _N_
then ID=ID+ &initialyear + &repricingfrequency;
Then take that code that I posted where you flag up on the new dataset when
ID = Column header.
I will give this a go tomorrow in arrays when i'm less tired
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.