DATA Step, Macro, Functions and more

conditional values based on column

Reply
Contributor
Posts: 65

conditional values based on column

[ Edited ]

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;
Super Contributor
Super Contributor
Posts: 266

Re: conditional values based on column

 

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. 

 

 

 

 

Contributor
Posts: 65

Re: conditional values based on column

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;
Super Contributor
Super Contributor
Posts: 266

Re: conditional values based on column

 

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?

 

 

 

 

Contributor
Posts: 65

Re: conditional values based on column

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

 

%Repricingperiod = 'Insert Years or Months Here';

Contributor
Posts: 65

Re: conditional values based on column

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

Ask a Question
Discussion stats
  • 5 replies
  • 104 views
  • 0 likes
  • 2 in conversation