How to make a calculation based on nested "if" conditions?

Reply
Occasional Contributor
Posts: 17

How to make a calculation based on nested "if" conditions?

Hi all,

I am trying to do the following calculation in SAS...sorry for the long text but wanted to describe it as much as I can and please let me know if you have questions if something is not very clear...

Lets say I have this data...

Maturity              ID#          Amt          Price

7/15/2020          355          5,000          5

1/15/2030          777          10,000        4

12/1/2043          435          5,000          3

1/1/2015            389          15,000        1

9/1/2050            278          25,000        2

and I have four sets of benchmark data that is attached as a file here - please let me know if the attachment is ok.

As you can see from the attached file I have four tabs (the data goes back many days/years) - main, early, mid, late.

So the logic of the calculation I am trying to do is:

- since I have as an example two dates 8/27/2013 and 8/26/2013 - then if date = 8/27/2013 then use the values from 8/26/2013

- if the year from the "maturity" column is prior to 2026 then based on the month from the maturity date I need the corresponding value

          * if month is Jan - Apr EARLY value

          * if month is May - Aug MID value

          * if month is Sept - Dec Late value

- but if the year from the "maturity" column is after 2026 then lookup the corresponding value in the MAIN table based on the year and since the last year in the "main table" is 2043, then if the maturity is after 2013 (like the 9/1/2050) then use the 2043 value (or last year value in the "main table")

So the calculations would be (PRICE-VALUE)*100 as follows for each maturity:

- 7/15/2020 - since the year is 2020 (less than 2026 which is the last year in each of the tables early, mid, late), then since the month is July I would need to look in the mid table where on 8/26/2013 the value for 2020 is 2.20 => (PRICE-VALUE)*100=(5-2.20)*100=280

- 1/15/2030 - since the year is 2030 (higher than 2026 which is the last year in each of the tables early, mid, late), then I would need to look in the main table where on 8/26/2013 the value for  2030 is 4 => (PRICE-VALUE)*100=(4-4)*100=0

- 1/1/2015 - since the year is 2015 (less than 2026 which is the last year in each of the tables early, mid, late), then since the month is Jan I would need to look in the early table where on 8/26/2013 the value for 2015 is 0.3=> (PRICE-VALUE)*100=(1-0.3)*100=70

....and so on.....

I was thinking to maybe have these five datasets in sas - my data, main, early, mid, late; and then in "my data" create a variable "VALUE" where I would have to populate the necessary values based on the logic above and then just do a quick calculation.

Let me know if you have any suggestions or different ideas about the set up as well as how I can program all these conditions.

Thanks for reading and helping me out!!!

Super Contributor
Posts: 307

Re: How to make a calculation based on nested "if" conditions?

One question: do your lookup tables (main, early, mid, late) have more than 3 columns? In the sample you've provided, there are only two date columns (plus one column for maturity year): one for August 26th and one for August 27th. I'd be interested to know how many columns we are potentially dealing with.

Occasional Contributor
Posts: 17

Re: How to make a calculation based on nested "if" conditions?

Fugue, yes the mid-early-late tables and the main tables as well have more than 3 columns. It actually starts from 1/1/2013 and I am adding new data every day. I also have same files for historical years. Also sometimes I need to use data from prior years (for example I have early-mid-late tables and main table going back from 2001). Just to give you more understanding this is because my initial data

Maturity              ID#          Amt          Price

7/15/2020          355          5,000          5

1/15/2030          777          10,000        4

12/1/2043          435          5,000          3

1/1/2015            389          15,000        1

9/1/2050            278          25,000        2

is actually series of bonds issued on the same day. So basically I need to get  the VALUE from the date prior to the issue date (that is why I was saying that for example is the issue date was 8/27/2013 I would need to get the according values from the day before which is 8/26/2013) - this basically shows the spread.

I need to work with similar data for bonds issued  on many different dates and years but it is always the VALUE I need from the day prior to the issue date.

Fugue and PGStats - I hope this helps. Please let me know if you need more clarifications.

Thanks!!!

Super Contributor
Posts: 307

Re: How to make a calculation based on nested "if" conditions?

Been busy so not much time to participate on the SAS forums . . . here is a solution that will work. Not the most elegant perhaps, but effective. It does not use an array as you originally suggested. Possible improvements could include passing the last year in the early/mid/late and main files to a macro for use in setting the lookup year and controlling the final joins. Hopefullly, I've interpreted your rules correctly.

Preparatory steps to ensure compatability with following logic:

1. in your "bond" file, ensure Maturity is a SAS date variable with variable name maturity_dt (or modify code below to suit your data)

2. in your "bond" file, create a variable called lookupyr. The logic is simple:

     if year ( maturity_dt ) >= 2043 then lookupyr = 2043;

     else lookupyr = year ( maturity_dt ) ;

3. year variable in main/early/mid/late (benchmark files) is named year (or modify code below to suit your data)

4. transpose benchmark files (main/early/mid/late) from wide to long. Former column names will now be a variable. Use Proc Transpose for this, and call the new variable (holding the former column names) rt_dt. Then, convert the resulting variable values to SAS date values.

5. change the issue date as needed

6. If you the "wide" format, you can transpose the output data from long to wide.

%let issuedt = 27Aug2013; /* entered as ddmonyyyy */

proc sql;
create table want_&issuedt as
select * , ( price - value )*100 as calc
from (
/* all maturity dates >= 2043 */
select h.*, m.*, 'MAIN' as reffile, 'GE 2043' as rule
from have h
  , main m
where h.lookupyr >= 2043
  and m.rt_dt = ("&issuedt"d -1)
  and h.lookupyr = m.year
/* maturity dates > 2026 (but less than 2043) */
UNION
select h.*, m.*, 'MAIN' as reffile, 'GT 2026' as rule
from have h
  , main m
where 2026 < h.lookupyr < 2043
  and m.rt_dt = ("&issuedt"d -1)
  and h.lookupyr = m.year
/* EARLY: Jan - Apr maturity dates */
UNION
select h.*, m.*, 'EARLY' as reffile, 'LE 2026' as rule
from have h
  , early m
where h.lookupyr <= 2026
  and month ( h.maturity_dt ) <= 4
  and m.rt_dt = ("&issuedt"d -1)
  and h.lookupyr = m.year
/* MID: May - Aug maturity dates */
UNION
select h.*, m.*, 'MID' as reffile, 'LE 2026' as rule
from have h
  , mid m
where h.lookupyr <= 2026
  and 5 <= month ( h.maturity_dt ) <= 8
  and m.rt_dt = ("&issuedt"d -1)
  and h.lookupyr = m.year
/* LATE: SEP - DEC maturity dates */
UNION
select h.*, m.*, 'LATE' as reffile, 'LE 2026' as rule
from have h
  , late m
where h.lookupyr <= 2026
  and 8 <= month ( h.maturity_dt )
  and m.rt_dt = ("&issuedt"d -1)
  and h.lookupyr = m.year
)
order by id
;
quit;

Message was edited by: Michael McCormick Forgot . . . you should add an ID variable to your "bond" file so that you can match final output data to initial input data.

Respected Advisor
Posts: 4,920

Re: How to make a calculation based on nested "if" conditions?

Can you rephrase the statement

- since I have as an example two dates 8/27/2013 and 8/26/2013 - then if date = 8/27/2013 then use the values from 8/26/2013

I don't understand what you mean.

PG

PG
Ask a Question
Discussion stats
  • 4 replies
  • 400 views
  • 0 likes
  • 3 in conversation