Recalculating observations in a database

[Jorge Cadenasso]

Hi,

I'm using a database and I need to recalculate the observations of a variable which shows trade values in US dollars. The thing is that this variable covers several years, and for each year I need to convert the values to year 2010 US dollars. Any tips?

Thanks in advance

[Ali Atia]

One option is to use -cpigen-, which is written by Austin Nichols and available via -ssc install cpigen-.

clear
set obs 63
set seed 63
gen year = _n+1949
gen nominal_USD = runiformint(1000,9999)
cpigen
egen cpi_2010 = max(cond(year==2010,cpi,.))
gen real_2010_USD = (nominal_USD/cpi)*cpi_2010
drop cpi*

. list in 57/63,noobs separator(0) abbrev(13)

  +------------------------------------+
  | year   nominal_USD   real_2010_USD |
  |------------------------------------|
  | 2006          6844        7402.319 |
  | 2007          8788        9241.807 |
  | 2008          8265        8370.805 |
  | 2009          8335        8472.021 |
  | 2010          7955            7955 |
  | 2011          8838        8566.928 |
  | 2012          9844        9349.349 |
  +------------------------------------+

[Jorge Cadenasso]

Thank you very much, Ali. However, I can't make STATA to recognize 2010 as the base year. Any idea why that is?

[Ali Atia]

I'm not sure I understand the problem - can you share what you typed into Stata and the results, plus a data example using the -dataex- command?

[Jorge Cadenasso]

I typed:

clear
set obs 21639
set seed 21639
gen year = _n+1949
gen nominal_USD = runiformint(1000,9999)
cpigen
egen cpi_2010 = max(cond(year==2010,cpi,.))
gen real_2010_USD = (nominal_USD/cpi)*cpi_2010
drop cpi*

Results:

. clear

. set obs 21639
number of observations (_N) was 0, now 21,639

. set seed 21639

. gen year = _n+1949

. gen nominal_USD = runiformint(1000,9999)

. cpigen
Variables month and quarter missing, annual CPI will be generated
(21576 missing values generated)

. egen cpi_2010 = max(cond(year==2010,cpi,.))

. gen real_2010_USD = (nominal_USD/cpi)*cpi_2010
(21,576 missing values generated)

. drop cpi*

.
end of do-file

dataex command:

* Example generated by -dataex-. To install: ssc install    dataex
clear
input float(year nominal_USD real_2010_USD)
1950 4361 34468.082
1951 6993  51249.24
1952 4735 33927.906
1953 6216  44178.05
1954 8345  59129.44
1955 8152  57922.68
1956 5915  41395.17
1957 5875  39784.52
1958 4658 30706.246
1959 3584 23389.797
1960 4712  30310.56
1961 1610 10246.737
1962 2753 17313.723
1963 5743  35674.66
1964 8003  49087.77
1965 9582  57852.93
1966 6012  35235.38
1967 1017  5799.627
1968 1729  9493.785
1969 3335  17522.07
1970 1782  8931.892
1971 7553 36276.715
1972 7431 34645.758
1973 1046  4587.493
1974 1150  4586.868
1975 9463  34856.06
1976 6920  24116.01
1977 6676  21866.79
1978 1064  3263.049
1979 5866 16423.412
1980 9657  24326.68
1981 4236  9746.324
1982 4803 10422.187
1983 3939   8197.08
1984 5570 11131.323
1985 2496 4822.3643
1986 7891 14980.087
1987 7065 12969.604
1988 4752  8416.601
1989 5708  9690.344
1990 5092  8236.249
1991 1173   1831.14
1992 4443  6765.271
1993 2786  4139.438
1994 8883 12926.364
1995 3284 4666.0845
1996 1883   2605.66
1997 6529  8844.028
1998 5525    7382.2
1999 9331 12208.213
2000 6314  7994.959
2001 6269   7721.04
2002 4599  5575.044
2003 7497  8886.854
2004 9126 10535.267
2005 8457  9443.831
2006 9898  10705.46
2007 1449 1523.8253
2008 2045 2071.1792
2009 9313  9466.099
2010 5513      5513
2011 2134 2068.5476
2012 5200  4938.705
2013 6797         .
2014 3204         .
2015 3820         .
2016 2525         .
2017 8178         .
2018 6111         .
2019 9704         .
2020 6874         .
2021 2995         .
2022 5251         .
2023 3732         .
2024 5542         .
2025 1177         .
2026 5293         .
2027 9208         .
2028 2839         .
2029 1726         .
2030 6523         .
2031 1441         .
2032 3340         .
2033 3859         .
2034 4093         .
2035 9039         .
2036 8075         .
2037 3392         .
2038 9012         .
2039 3014         .
2040 3107         .
2041 7049         .
2042 5848         .
2043 6847         .
2044 3246         .
2045 5949         .
2046 9162         .
2047 4901         .
2048 8630         .
2049 5197         .
end

copy up to and including the previous line - ----------------

Listed 100 out of 21639 observations
Use the count() option to list more

[Ali Atia]

I can't see anything wrong with that output. 2010 is the base year, which you can confirm by comparing the nominal value in 2010 to the real value in 2010 USD and seeing that they're the same. The missing observations are there because cpigen only goes up to 2013.

[Jorge Cadenasso]

When I write your code, it erases the entire dataset and only the new data is on STATA. Also, I need the data to be between 2008 and 2019. Do you know other code?

[Ali Atia]

To answer your first question, my code was just an example using random data. You need to apply it to your dataset:

clear
use yourdataset.dta
cpigen
egen cpi_2010 = max(cond(year==2010,cpi,.))
gen real_2010_USD = (INSERT_YOUR_NOMINAL_VARIABLE_HERE/cpi)*cpi_2010
drop cpi*

As to your second question, for data after 2013 you might try manually downloading CPI data from the Federal Reserve repository or the Bureau of Labor Statistics and merging it with your dataset. Or maybe someone else will suggest a more up-to-date user-written command I don't know about.

[Jorge Cadenasso]

Thank you, Ali

I have the data beyond 2013, but I don't know what to do so that the deflator applies to it.

[Jorge Cadenasso]

Any ideas, anyone?

[Jorge Cadenasso]

So this database has values from 2008 to 2019 for 19 countries. One of the variables is trade (import and export) in US dollars (nominal), and there's no information in other currencies. I need to express those values in 2010 US dollars. If anyone could help me, it would be much appreciated.

[Ali Atia]

As I mentioned in #6, cpigen only has cpi data up to 2013. As it turns out, another command called freduse (mentioned in the also see section of the cpigen helpfile) exists which allows you to download more up-to-date data from the FRED repository. You could try something along these lines:

clear
freduse CPALTT01USA661S
rename CPALTT01USA661S cpi
gen year = yofd(daten)
keep cpi year
drop if year<2008 | year>2019
egen cpi_2010=max(cond(year==2010,cpi,.))
tempfile temp
save `temp'
clear
use yourdataset.dta
merge m:1 year using `temp',nogen keep(1 3)
gen real_2010_USD = (INSERT_YOUR_NOMINAL_VARIABLE_HERE/cpi)*cpi_2010

You'll need to install freduse from ssc first.

[Jorge Cadenasso]

Thank you, Ali!