topic Re: Currency Conversion in SAS Programming

Currency Conversion

Theo_Gh — Wed, 09 Aug 2017 18:21:42 GMT

Dear Everyone,

I am working with data from over 40 countries and have to convert all the local currencies into USD. I have extracted the Exchange Rate Daily (EXRT_DLY) from Compustat.

How can I convert the data from various countries from the local currencies into USD using SAS codes ?

Thank you.

Re: Currency Conversion

novinosrin — Wed, 09 Aug 2017 18:28:27 GMT

Please give us a sample of your Input1- Yours data with 40 countries and your look up dataset that has exchange rate daily. The immediate approach comes to my mind is load the exchange rate table in hash table and one pass on your data looking up the hash table should be a breeze

Re: Currency Conversion

Reeza — Wed, 09 Aug 2017 18:43:34 GMT

You can use a lookup table approach via a merge on the currency type.

Once the merge brings in the conversion rate you can do a straight multiplication.

If you'd like more than a general answer, post specific details about your situation including sample input and expected output data. What you've tried is a good idea too.

Re: Currency Conversion

Theo_Gh — Wed, 09 Aug 2017 19:14:05 GMT

The data from the countries are annual and look like this:

gvkey datadate fyear curcd assets liabilities cash etc

000 20000101 2000 EUR 10 20 50

The Exchange Rate Daily (EXRT_DLY) is a daily (from Jan to Dec) data and looks like this:

Obs tocurd exratd fromcurd datadate

1 AED 7.9470 GBP 20000101

Thanks

Re: Currency Conversion

Reeza — Wed, 09 Aug 2017 19:16:55 GMT

So a SQL merge is the correct approach. You will need to determine the fields to join on and then use a approach listed here:

https://stats.idre.ucla.edu/sas/modules/match-merging-data-files-using-proc-sql/

Re: Currency Conversion

Theo_Gh — Wed, 09 Aug 2017 19:29:59 GMT

Will try and get back to you with the outcome.
Thanks

Re: Currency Conversion

Kurt_Bremser — Thu, 10 Aug 2017 06:33:51 GMT

From the currency conversion table, create a cntlin file for proc format (start = currency id, label = conversion factor), and use the resulting format that with one of the input/put functions. That makes any sorting/merging unnecessary.

If you need example code, provide some example data for both tables.

Re: Currency Conversion

Theo_Gh — Thu, 10 Aug 2017 06:38:28 GMT

@Reeza @novinosrin I'm having difficulties in the currency conversion. The conversion from a local currency to USD is a two-step process: 1. from local currency to GBP and from GBP to USD. So for instance, to convert HKD to USD, WRDS uses this code:

proc sql;

SELECT a.datadate, a.exratd AS hkd_exratd, b.exratd AS usd_exratd, ( 1 / a.exratd ) * b.exratd AS exchange_rate

FROM comp.g_exrt_dly a, comp.g_exrt_dly b

WHERE ( a.tocurd = 'HKD' ) AND ( a.fromcurd = 'GBP' )

AND ( b.tocurd = 'USD' )

AND ( b.fromcurd = 'GBP' )

AND a.datadate = b.datadate

order by datadate desc;

quit;

The code above will give you a table that looks like this:

datadate exchange rate daily exchange rate daily exhange_rate

20140222 12.9430 1.6687 0.128926833

So, if I'm not mistaken 'exchange_rate' is the rate between HKD and USD, the rate to use in the conversion. My question is do I have to write a separate code for all the countries separately or I can write one code to incorporate all. And how do I then use this to finally convert my data from local currencies into USD ?

Thank you.

Re: Currency Conversion

Theo_Gh — Thu, 10 Aug 2017 09:09:26 GMT

Thank very much for your time and patience. I have attached the datasets. The currencies here are HKD and EURO together with the annual fundamentals.

Re: Currency Conversion

novinosrin — Thu, 10 Aug 2017 14:19:27 GMT

@Theo_Gh OK, I will try to work on your stuff. However, Today I have got lectures and some studying to do for my exam next week. If time permits, I will seriously help you out, but please do not totally rely on me. I am sure some Super user will respond with a code solution regardless.

Re: Currency Conversion

Reeza — Thu, 10 Aug 2017 17:51:50 GMT

@Theo_Gh it helps if you make it easier to help you.

You can do this by providing sample data in a data step, not attached files. I'm 90% less likely to download a file to work with rather than just copy and paste data from a website.

You can find instructions on how to attach your data in this form on here.

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Re: Currency Conversion

Reeza — Thu, 10 Aug 2017 17:51:53 GMT

@Theo_Gh it helps if you make it easier to help you.

You can do this by providing sample data in a data step, not attached files. I'm 90% less likely to download a file to work with rather than just copy and paste data from a website.

You can find instructions on how to attach your data in this form on here.

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Re: Currency Conversion

mkeintz — Thu, 10 Aug 2017 18:11:27 GMT

I would make a hash table of the exchange rates, keys of currency_id and date.

Let's say you have a data set EXCHRATES with 3 variables: CURRENCY, DATE, and EXCH_DLY (where exch_dly is amount of other currency per USD).

And also dataset HAVE with variables CURRENCY DATE, and other vars of interest (say MARKET_VAL in local currency

data want;

 data want;
  if _n_=1 then do;
   if 0 then set exchrates;
   declare hash h (dataset:'exchrates');
     h.definekey('currency','date');
     h.definedata('exch_dly');
     h.definedone();
  end;

  set have;
  if currency='USD' then usd_sales=sales;
  else if h.find() = 0 then usd_sales=sales/exch_dly;
run;

Important point: if the variable USD_SALES is a NEW variable then the code above is fine, no matter whether the H.find() method finds a currency/date combination or not

But if USD_SALES is an incoming variables, then add a second "else" condition:

  else usd_sales=.;

Otherwise instances of unfound currency/date combinations will inherit the USD_SALES value from the most recent successful H.find() method.

Re: Currency Conversion

Theo_Gh — Fri, 11 Aug 2017 07:35:54 GMT

Thank you. Still new at this; will learn. Thank you once again.