BookmarkSubscribeRSS Feed
Theo_Gh
Obsidian | Level 7

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. 

13 REPLIES 13
novinosrin
Tourmaline | Level 20

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
Super User

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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7
Will try and get back to you with the outcome.
Thanks
Kurt_Bremser
Super User

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
Obsidian | Level 7

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
Tourmaline | Level 20

@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
Super User

@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
Obsidian | Level 7
Thank you. Still new at this; will learn. Thank you once again.
Reeza
Super User

@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
Obsidian | Level 7

@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
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 13 replies
  • 4916 views
  • 0 likes
  • 5 in conversation