Sayda--
At 03:42 PM 10/2/2006, Sayda G Wentworth wrote:
>I'd like to convert those nominal earnings to 2003 dollars.
>But the results I get are incorrect. To check why, I try:
>gen realearnings1951=earnings1951/cpi[1,1]
On 10/3/06, David Kantor <[email protected]> wrote:
Nick Cox has already answered this, but I would add that you have the
choice of loading the CPI values in a matrix or in data, but you
need to decide that clearly, and then code accordingly.
Nick's answer (redacted) was:
forval i = 1951/2003 {
gen realearnings`i' = earnings`i' / cpi[`=`i' - 1950']
}
But...
I would caution you against copying the cpi into a variable (in the
Editor window?) from Excel--this is dangerous for various reasons, and
does not produce an easy way to deflate nominal dollars, Nick's
solution notwithstanding (think what happens after you accidentally
change the sort order and then try to deflate something).
Instead, make a new dataset with a time variable and the relevant cpi
measures, then merge using the time vars. To do that, you will also
want the earnings data in "long" form (see -help reshape-), which will
help you in other ways, I suspect. (This is SER data, right?)
Another caveat: think about what you want to deflate by (a concern
alluded to by David Kantor). If you go with the CPI instead of an
earnings index (such as the one used by the US SSA to compute
benefits), at least use the research series where possible. You can
get the CPI-U-RS from http://www.bls.gov/cpi/cpiurstx.htm back to
1978, then get the CPI-U (Series Id: CUUR0000SA0) back to 1913,
inflate the CPI-U by 100/62.1 so that the two series are the same in
Dec 1977, and then merge the CPI-U onto the CPI-U-RS (discarding more
recent values of the CPI-U in favor of the CPI-U-RS), then divide all
by the value of that new variable in 2003. The output from this
method is pasted below in a format that facilitates usage...
***********Appendix***********
input yr cpi_u cpi_u_rs cpi cpi03
1913 15.94203 . 15.94203 .0595074
1914 16.10306 . 16.10306 .0601085
1915 16.26409 . 16.26409 .0607096
1916 17.55234 . 17.55234 .0655182
1917 20.61192 . 20.61192 .0769388
1918 24.31562 . 24.31562 .0907638
1919 27.85829 . 27.85829 .1039877
1920 32.20612 . 32.20612 .120217
1921 28.82448 . 28.82448 .1075942
1922 27.05314 . 27.05314 .1009822
1923 27.53623 . 27.53623 .1027855
1924 27.53623 . 27.53623 .1027855
1925 28.18036 . 28.18036 .1051898
1926 28.50241 . 28.50241 .106392
1927 28.01932 . 28.01932 .1045887
1928 27.53623 . 27.53623 .1027855
1929 27.53623 . 27.53623 .1027855
1930 26.89211 . 26.89211 .1003812
1931 24.47665 . 24.47665 .0913649
1932 22.06119 . 22.06119 .0823486
1933 20.93398 . 20.93398 .078141
1934 21.5781 . 21.5781 .0805454
1935 22.06119 . 22.06119 .0823486
1936 22.38325 . 22.38325 .0835508
1937 23.18841 . 23.18841 .0865562
1938 22.70531 . 22.70531 .0847529
1939 22.38325 . 22.38325 .0835508
1940 22.54428 . 22.54428 .0841519
1941 23.6715 . 23.6715 .0883595
1942 26.24799 . 26.24799 .0979768
1943 27.85829 . 27.85829 .1039877
1944 28.34138 . 28.34138 .1057909
1945 28.98551 . 28.98551 .1081953
1946 31.40097 . 31.40097 .1172115
1947 35.90982 . 35.90982 .1340419
1948 38.80837 . 38.80837 .1448614
1949 38.32528 . 38.32528 .1430582
1950 38.80837 . 38.80837 .1448614
1951 41.86795 . 41.86795 .156282
1952 42.67311 . 42.67311 .1592875
1953 42.99517 . 42.99517 .1604896
1954 43.31723 . 43.31723 .1616918
1955 43.1562 . 43.1562 .1610907
1956 43.80032 . 43.80032 .163495
1957 45.2496 . 45.2496 .1689048
1958 46.53784 . 46.53784 .1737135
1959 46.85991 . 46.85991 .1749157
1960 47.66506 . 47.66506 .1779211
1961 48.14815 . 48.14815 .1797243
1962 48.63124 . 48.63124 .1815276
1963 49.27536 . 49.27536 .1839319
1964 49.91948 . 49.91948 .1863363
1965 50.72464 . 50.72464 .1893417
1966 52.17391 . 52.17391 .1947515
1967 53.78422 . 53.78422 .2007623
1968 56.03865 . 56.03865 .2091775
1969 59.09823 . 59.09823 .2205981
1970 62.47987 . 62.47987 .2332209
1971 65.21739 . 65.21739 .2434393
1972 67.31079 . 67.31079 .2512534
1973 71.49758 . 71.49758 .2668816
1974 79.38808 . 79.38808 .2963348
1975 86.63446 . 86.63446 .3233836
1976 91.62641 . 91.62641 .3420172
1977 97.58454 . 97.58454 .3642573
1978 104.992 104.3 104.3 .3893244
1979 116.9082 114.1 114.1 .4259052
1980 132.6892 126.7 126.7 .4729377
1981 146.3768 138.6 138.6 .5173572
1982 155.3945 146.8 146.8 .5479657
1983 160.3865 152.9 152.9 .5707353
1984 167.3108 159 159 .593505
1985 173.2689 164.3 164.3 .6132886
1986 176.4895 167.3 167.3 .6244868
1987 182.9308 173 173 .6457633
1988 190.4992 179.3 179.3 .6692796
1989 199.6779 187 187 .6980217
1990 210.467 196.3 196.3 .7327361
1991 219.3237 203.4 203.4 .7592385
1992 225.9259 208.5 208.5 .7782755
1993 232.6892 213.7 213.7 .7976857
1994 238.6473 218.2 218.2 .814483
1995 245.4106 223.5 223.5 .8342665
1996 252.657 229.5 229.5 .8566629
1997 258.4541 234.4 234.4 .8749533
1998 262.4799 237.7 237.7 .8872714
1999 268.277 242.7 242.7 .905935
2000 277.2947 250.8 250.8 .9361702
2001 285.1852 257.8 257.8 .9622993
2002 289.694 261.9 261.9 .9776036
2003 296.2963 267.9 267.9 1
2004 304.1868 275.1 275.1 1.026876
2005 314.4928 284.3 284.3 1.061217
end
keep yr cpi03
save /cpi03
use yourdata
gen n=_n
reshape long earnings, i(n) j(yr)
sort yr
merge yr using /cpi03
gen rearn=earn/cpi03
*
* For searches and help try:
* http://www.stata.com/support/faqs/res/findit.html
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
