08-22-2016 04:30 PM
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.
create table Temp_Conversion_Table as
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;
create table Month_End_Close_Conversion_Table as
group by currency_id,
having exchange_date_calc_value = min(exchange_date_calc_value);
08-23-2016 03:04 AM
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.