CODEWITHSUNDEEP
sqlsascurrency-exchange-rates
Noza
Noza

Reputation: 35

Currency exchange calculation in SAS

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.

Upvotes: 0

Views: 484

Answers (2)

Chris J
Chris J

Reputation: 7769

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

Upvotes: 0

Andy N
Andy N

Reputation: 1304

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'

Upvotes: 1

Related Questions