DATA Step, Macro, Functions and more

Currency Conversion

Reply
Contributor
Posts: 50

Currency Conversion

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. 

PROC Star
Posts: 283

Re: Currency Conversion

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

Super User
Posts: 19,851

Re: Currency Conversion

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.

 

Contributor
Posts: 50

Re: Currency Conversion

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

Super User
Posts: 19,851

Re: Currency Conversion

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/

Contributor
Posts: 50

Re: Currency Conversion

Will try and get back to you with the outcome.
Thanks
Super User
Posts: 7,832

Re: Currency Conversion

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 50

Re: Currency Conversion

Posted in reply to KurtBremser

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.

PROC Star
Posts: 283

Re: Currency Conversion

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

Super User
Posts: 19,851

Re: Currency Conversion

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

Contributor
Posts: 50

Re: Currency Conversion

Thank you. Still new at this; will learn. Thank you once again.
Super User
Posts: 19,851

Re: Currency Conversion

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

Contributor
Posts: 50

Re: Currency Conversion

@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. 
Trusted Advisor
Posts: 1,022

Re: Currency Conversion

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.

Ask a Question
Discussion stats
  • 13 replies
  • 312 views
  • 0 likes
  • 5 in conversation