Currency Conversion

[RickyS]

Good afternoon still struggling with something I started a while ago, so starting a new message.

I need to join 2 tables, exchange_rates and summary, in order to convert local currencies to usd.  The tables can be joined on

exchange_rates.currency_id = summary.transaction_currency_id. 

The challenge is finding the right exchange rate based on identifying the closest exchange_rate_date to Month_End_Close_Date to find the correct currency conversion rate to use given many to many relationships.

Here is what I have come up with attatching the data sets.

proc sql;
create table Temp_Conversion_Table as 
select a.exchange_rate_id,
       a.currency_id,
       b.month_end_close_date,
       a.exchange_rate,
       a.Exchange_Rate_Date,
       abs(a.exchange_rate_date - b.month_end_close_date) as exchange_date_calc_value /* calculates absolute value for exchange rate date to month end close date by currency */
from Exchange_Rates a
left join SUMMARY b
     on a.currency_id = b.transaction_currency_id
where b.month_end_close_date is not null
order by a.currency_id asc, a.Exchange_Rate_Date, b.month_end_close_date;
quit;

proc sql;
create table Month_End_Close_Conversion_Table as
select currency_id, 
       Exchange_Rate_Date,
    month_end_close_date,
       exchange_rate,
       exchange_date_calc_value
from Temp_Conversion_Table
group by currency_id,
         Exchange_Rate_Date,
      month_end_close_date
having exchange_date_calc_value = min(exchange_date_calc_value); 
quit;

[Kurt_Bremser]

Please provide example data in a reasonable format. Don't expect people to open a Excel(!) file from the internet. Use a data step with cards;

My preferred method would be:

- sort the summary dataset by currency

- in a data step "by currency", at every first.currency create a hash object with your exchange rates from the respective currency

- scan through the hash to find the closest exchange rate date; since this happens in memory, this is probably best, performancewise

Now, if your month_end_close_date is unique for every month, one could first extract all currency exchange rate observations that are closest to this date for every month, and then simply join on currency and month.