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   Year42017   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;

then return 1;
else if column_header <1 or >1
then return 0;

run;``````
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
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

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