turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- How to make a calculation based on nested "if" con...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-27-2013 05:27 PM

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!!!

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to letsdoit

08-27-2013 07:03 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Fugue

08-28-2013 12:45 AM

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!!!

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to letsdoit

08-29-2013 04:59 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to letsdoit

08-27-2013 08:53 PM

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