Currency Conversion

[Theo_Gh]

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. 

[novinosrin]

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

[Reeza]

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.

[Theo_Gh]

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

[Reeza]

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/

[Theo_Gh]

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

[Kurt_Bremser]

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.

[Theo_Gh]

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.

[novinosrin]

@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. 

[Reeza]

@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-dat...

[Theo_Gh]

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

[Reeza]

@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-dat...

[Theo_Gh]

@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. 

[mkeintz]

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.