BookmarkSubscribeRSS Feed
89974114
Quartz | Level 8

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;
5 REPLIES 5
HB
Barite | Level 11 HB
Barite | Level 11

 

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. 

 

 

 

 

89974114
Quartz | Level 8

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;
HB
Barite | Level 11 HB
Barite | Level 11

 

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?

 

 

 

 

89974114
Quartz | Level 8

I guess the only way to really know is by introducing a macro variable

 

%Repricingperiod = 'Insert Years or Months Here';

89974114
Quartz | Level 8

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 683 views
  • 0 likes
  • 2 in conversation