BookmarkSubscribeRSS Feed
RickyS
Quartz | Level 8

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;

 

 

 

1 REPLY 1
Kurt_Bremser
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 1478 views
  • 0 likes
  • 2 in conversation