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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.