Currency exchange calculation in SAS

Question

I have two tables first one Account consist of following data:

Code | Exposure | Expo_Curr | Limit | Limit_curr | Date_extr  

2105 | 2.354586 | EUR | 288.6 | HUF | 1405  
2105 | 25.46658 | USD | 12.32 | CAD | 1203  
2105 | 5.987456 | CAD | 321.2 | CZK | 1107  
2105 | 9.658785 | HRK | 5.365 | EUR | 1103

Second table consist of exchange rate

Code | date_extr | currency_from | currency_to | fx_rate  

2105 | 1405 | HUF | EUR | 4.36  
2105 | 1203 | USD | EUR | 3.62  
2105 | 1203 | CAD | EUR | 1.23  
2105 | 1107 | CAD | EUR | 1.17  
2105 | 1107 | CZK | EUR | 24.6  
2105 | 1103 | HRK | EUR | 35.6

I need to create table where Exposure and Limit will be transferred to EUR currency according to exchange rate in the second table. If the data is already in EUR it should be just multiplied to 1 the rest should be calculated according to rate in second table. The rate should match also the date_extr (YYMM when rate was valid).

Should look like this:

Code | Exposure | Expo_Curr | Limit | Limit_curr | Date_extr  

2105 | 2.354586*1.00 | EUR | 288.6*4.36 | HUF | 1405  
2105 | 25.46658*3.62 | USD | 12.32*1.23 | CAD | 1203  
2105 | 5.987456*1.17 | CAD | 321.2*24.6 | CZK | 1107  
2105 | 9.658785*35.6 | HRK | 5.365*1.00 | EUR | 1103

I'm working on SAS, so I tried to do it with SQL join, but I couldn't make it work. If anyone can help to figure out how could I do it? I have more columns to calculate like this.

Thank you in advance.

score 1 · Answer 1 · answered Aug 05 '16 at 09:48

1

You just need to left join on the FX table twice. One for limit, one for exposure.

E.g.

SELECT
  CASE
    WHEN acc.expo_curr = 'EUR'
    THEN acc.exposure
    ELSE acc.exposure * expo.fx_rate
  END AS exposure,
  acc.expo_curr,
  CASE
    WHEN acc.limit_curr = 'EUR'
    THEN acc.limit
    ELSE acc.limit * lim.fx_rate
  END AS limit,
  acc.limit_curr
FROM account acc
LEFT JOIN exchange expo
  ON  expo.date_extr = acc.date_extr
  AND expo.currency_from = acc.expo_curr
  AND expo.currency_to = 'EUR'
LEFT JOIN exchange lim
  ON  lim.date_extr = acc.date_extr
  AND lim.currency_from = acc.limit_curr
  AND lim.currency_to = 'EUR'

answered Aug 05 '16 at 09:48

Andy N

1,238
1
12
30

Thank you Andy, is there any other option, because in fact I need to calculate 5 columns like this from 1 table and having 5 joins will take long time, because Account table is quite big. Thanx in advance! – Noza Aug 05 '16 at 10:21
Look into Hash Tables, seems like an ideal candidate for this type of problem. – Chris J Aug 05 '16 at 10:32
@Noza don't worry about the Account table's size. Using this query you'll only conduct a single table-scan on it. The issue (in this case) would come if your FX tables were very large. – Andy N Aug 05 '16 at 10:45
..if the 5 amounts in the Account table were stored in 5 rows rather than 5 columns, the solution might be neater. – Andy N Aug 05 '16 at 10:49
@AndyN thank you, I can't change the table Account structure, but I will try to run it with the sample you gave me. thank you very much. – Noza Aug 05 '16 at 10:52
@ChrisJ can you show me some small sample? I have never used hashes. may be it could be another solution? – Noza Aug 05 '16 at 10:53

score 0 · Accepted Answer · answered Aug 05 '16 at 12:09

You could adopt a hash-table approach, and extend the hash keys and corresponding lookups to as many match-variables as you require :

data want ;
  if _n_ = 1 then do ;
    /* Define & load hash table of conversions */
    length currency_from $3. date_extr fx_rate 8. ;
    declare hash exc (dataset:"exchange") ;
    exc.defineKey('currency_from','date_extr') ; /* extend this to more variables */
    exc.defineData('fx_rate') ;
    exc.defineDone() ;
    call missing(of currency_from--fx_rate) ;
  end ;

  set accounts ;

  /* Lookup Expo_Curr + date_extr in hash table */
  rc = exc.find(key:expo_curr,key:date_extr) ; /* extend this to match */
  if rc = 0 then do ;
    expo_rate = fx_rate ;
    exposure2 = exposure * expo_rate ;
  end ;

  /* Lookup Limit_Curr + date_extr in hash table */
  rc = exc.find(key:limit_curr,key:date_extr) ; /* extend this to match */
  if rc = 0 then do ;
    limit_rate = fx_rate ;
    limit2 = limit * limit_rate ;
  end ;

  drop rc ;
run ;

https://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a003143739.htm

thank you very much! It works :) I will try to learn more about hashing now. — Noza, Aug 05 '16 at 12:55

Currency exchange calculation in SAS

2 Answers2