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;
... View more