topic Currency conversion based on closest exchange rate date to transaction date EG 6.1 in SAS Enterprise Guide

Currency conversion based on closest exchange rate date to transaction date EG 6.1

RickyS — Wed, 27 Jul 2016 15:13:20 GMT

Need to convert transaction values when if and when they are stored in local currency at the closest time I can make a conversion I.e. when the transaction took place

Exchange rate table exists with approxiamtely (15) different Currency_Id values, it is updated somewhat inconsistently but normally 8 or 9 times annually:

Exchange_Rate_Id Exchange_Rate Currency_Id Exchange_Date

1 1.63 15 01/012013

2 .96 14 05/01/2016

3 1.75 15 05/012015

Abbreviated view of data set 2:

Transaction Date Currency_id Transaction_Value

01/23/13 15 15414

05/24/16 14 500

03/15/15 15 750

I cannot use a max date solution for any currency coupled with a straight inner join as the most recent exchange rate may vary significantly from the transaction date, Brexit impact.

I really need to find the closest exchange date to the transaction date which maybe in the same month, in the prior month or in the upcoming month. I need to convert 17K records spread out over 5 year period.

Any help would be appreciated.

Re: Currency conversion based on closest exchange rate date to transaction date EG 6.1

TomKari — Wed, 27 Jul 2016 15:53:33 GMT

Hi, Ricky

Here's a not particularly elegant but fairly simple solution. Description follows the code:

data All_Dates;

do Currency_Id = 1 to 15;

do Exchange_Date = '1jan2012'd to '31dec2016'd;

format Exchange_Date date.;

output;

end;

run;

proc sort data=All_Dates;

by Currency_Id Exchange_Date;

run;

data Rates;

informat Exchange_Date mmddyy10.;

format Exchange_Date date.;

input Exchange_Rate_Id Exchange_Rate Currency_Id Exchange_Date;

cards;

1 1.63 15 01/01/2013

2 .96 14 05/01/2016

3 1.75 15 05/01/2015

run;

proc sort data=Rates;

by Currency_Id Exchange_Date;

run;

data Combined_Rates;

merge Rates All_Dates;

by Currency_Id Exchange_Date;

drop Exchange_Rate_Id;

run;

data Comprehensive_Rates;

retain Before_Date Before_Rate;

format Before_Date date.;

set Combined_Rates;

by Currency_Id;

if first.Currency_Id then

call missing(Before_Date, Before_Rate, Before_Closeness);

if ^missing(Exchange_Rate)

then do;

Before_Date = Exchange_Date;

Before_Rate = Exchange_Rate;

Before_Closeness = 0;

end;

else Before_Closeness = Exchange_Date - Before_Date;

run;

proc sort data=Comprehensive_Rates;

by Currency_Id descending Exchange_Date;

run;

data Comprehensive_Rates;

retain After_Date After_Rate;

format After_Date date.;

set Comprehensive_Rates;

by Currency_Id;

if first.Currency_Id then

call missing(After_Date, After_Rate, After_Closeness);

if ^missing(Exchange_Rate)

then do;

After_Date = Exchange_Date;

After_Rate = Exchange_Rate;

After_Closeness = 0;

end;

else After_Closeness = After_Date - Exchange_Date;

run;

First data step creates a dataset with every combination of date and Currency_Id in your environment (I assumed 2012 to 2016, 1 to 15). Then sort it by ID / Date.

Second data step, input your rates. Also, sort by ID / Date. Make sure you don't have duplicate ID / Date combinations, or the rest won't work.

Third data step, merge the above two. You should end up with one record for every day / ID combination, but with values in Rate only for the days that have rates from your input dataset in step 2.

Fourth data step, run through the output from above. Whenever you hit a rate, start tracking the rate, the date that the rate was set, and how many days between the current record and the date the rate was set. These are in the "Before" variables.

Then sort by ID / descending Date, and do the same thing to get the duration between the current record and the dates when rates are set after the date. These are the "After" variables.

You should have everything you need to pick a rate.

Tom

Re: Currency conversion based on closest exchange rate date to transaction date EG 6.1

Ksharp — Thu, 28 Jul 2016 03:30:06 GMT


If you don't have a big table , you could try SQL :


data All_Dates;
do Currency_Id = 1 to 15;
do Exchange_Date = '1jan2012'd to '31dec2016'd;
format Exchange_Date date.;
output;
end;
end;
run;

data Rates;
informat Exchange_Date mmddyy10.;
format Exchange_Date date.;
input Exchange_Rate_Id Exchange_Rate Currency_Id Exchange_Date;
cards;
1 1.63 15 01/01/2013
2 .96 14 05/01/2016
3 1.75 15 05/01/2015
;
run;
proc sql;
create table want as
 select a.*,Exchange_Rate 
  from All_Dates as a left join Rates as b
   on a.Currency_Id=b.Currency_Id and a.Exchange_Date > b.Exchange_Date
    group by a.Currency_Id,a.Exchange_Date 
     having a.Exchange_Date-b.Exchange_Date=min(a.Exchange_Date-b.Exchange_Date);
quit;

Re: Currency conversion based on closest exchange rate date to transaction date EG 6.1

RickyS — Thu, 28 Jul 2016 15:01:46 GMT

Like where you are headed, the exchange rate information is captured in exchange_rate table so no input step needed the other work.summary_table has the monetary values agains the classifications post proc means.

Re: Currency conversion based on closest exchange rate date to transaction date EG 6.1

RickyS — Wed, 17 Aug 2016 14:07:01 GMT

Have one challenge, the new table that is created does not produce a distinct value for example there are 29 observations where the exchange_rate_date = 12/31/1995, month_end_close_date = 12/30/1995, currency_id =1

proc sql;
create table Exchange_Rate_Conversion_Table as
select a.currency_id,
    a.Exchange_Rate,
    a.Exchange_Rate_Date,
    b.Month_End_Close_date
from Exchange_Rates a
left join Summary b
   on a.Currency_Id = b.transaction_Currency_Id and a.Exchange_Rate_Date > b.Month_End_Close_Date
group by a.Currency_Id, a.exchange_rate_date
having a.exchange_rate_date - b.Month_End_Close_Date = min(a.exchange_rate_date - b.Month_End_Close_Date);
quit;

Re: Currency conversion based on closest exchange rate date to transaction date EG 6.1

Ksharp — Thu, 18 Aug 2016 02:10:58 GMT

Can you start a new session to discuss this. It is almost four month ago.
If you want distinct row, you can add DISTINCT keyword :

select  distinct  a.currency_id, ................