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.
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
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.
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
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/
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.
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.
@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.
@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.
@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.
@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:
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: