topic conditional values based on column in SAS Programming

conditional values based on column

89974114 — Wed, 28 Feb 2018 15:16:57 GMT

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;

Re: conditional values based on column

HB — Wed, 28 Feb 2018 17:24:42 GMT

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.

Re: conditional values based on column

89974114 — Wed, 28 Feb 2018 17:32:03 GMT

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;

Re: conditional values based on column

HB — Wed, 28 Feb 2018 17:42:55 GMT

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?

Re: conditional values based on column

89974114 — Wed, 28 Feb 2018 17:45:36 GMT

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

%Repricingperiod = 'Insert Years or Months Here';

Re: conditional values based on column

89974114 — Wed, 28 Feb 2018 18:02:52 GMT

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