Deflators (merge)

[Hugo Rocha]

Dear all,

I apologize for one more question today (many posts recently). I have two datasets that I would like to merge. One dataset is composed by deflators per year. The other dataset is a industry level dataset for countries (from 1963-2019) with many variables stated in current US dollars (that's why the need for deflators). Clearly, the merge is not 1:1, 1:m or m:1. The deflators are per year while the industry level dataset include many countries and industries. My goal is to have the same deflatos per year. I tried manually but was quite unsuccessful given the sample size. Is there a shortcut to put the industry level dataset with the deflators per year in the same dta?

 * Example generated by -dataex-. To install: ssc install dataex (deflators)
clear
input int year float(fixedassetsdeflator cpi)
1963  30.315  30.633
1964  30.513  31.038
1965  30.853  31.528
1966  31.327  32.471
1967  32.093  33.375
1968  33.237  34.792
1969  34.638  36.683
1970  36.296  38.842
1971  37.997  40.483
1972  39.297  41.808
1973  40.882  44.425
1974  44.857  49.317
1975  50.766  53.825
1976  53.562  56.933
1977  57.111  60.617
1978   60.93  65.242
1979   65.83  72.583
1980  71.641  82.383
1981  78.453  90.933
1982  82.911  96.533
1983  82.774  99.583
1984  83.036 103.933
1985  83.893   107.6
1986  85.365 109.692
1987  86.339 113.617
1988  88.514 118.275
1989  90.572 123.942
1990  92.516 130.658
1991  94.267 136.167
1992   93.96 140.308
1993  94.161 144.475
1994  94.904 148.225
1995  95.849 152.383
1996  95.267 156.858
1997  94.735 160.525
1998  93.248 163.008
1999  92.314 166.583
2000  92.718 172.192
2001  92.346 177.042
2002  91.863 179.867
2003  91.156     184
2004  92.055 188.908
2005  94.443 195.267
2006  96.745 201.558
2007   98.31 207.344
2008  99.832 215.254
2009  99.184 214.565
2010  97.416 218.076
2011  98.559 224.923
2012     100 229.586
2013 100.251 232.952
2014 101.469 236.715
2015 101.909 237.002
2016 101.119 240.005
2017 101.977 245.121
2018 102.815 251.099
2019 104.137 255.647
   .       .       .
   .       .       .
end

 * Example generated by -dataex-. To install: ssc install dataex (industry level dataset)
clear
input int(year country) str2 isic str3 isiccomb byte sourcecode long(Establishments Employment) double(Wages OutputINDSTAT4 ValueAdded GrossFixed) long FemaleEmployees str16 value51
1963 32 "15" "15"  3 .  248175          .           .          .         . . ""
1963 32 "16" "16"  3 .    7663          .           .          .         . . ""
1963 32 "17" "17"  3 .  128391          .           .          .         . . ""
1963 32 "18" "18A" 3 .   80920          .           .          .         . . ""
1963 32 "19" "18A" 3 .       .          .           .          .         . . ""
1963 32 "20" "20"  3 .   40204          .           .          .         . . ""
1963 32 "21" "21"  3 .   21031          .           .          .         . . ""
1963 32 "22" "22"  3 .   40594          .           .          .         . . ""
1963 32 "23" "23"  3 .   10799          .           .          .         . . ""
1963 32 "24" "24"  3 .   62886          .           .          .         . . ""
1963 32 "25" "25"  3 .   25112          .           .          .         . . ""
1963 32 "26" "26"  3 .   69982          .           .          .         . . ""
1963 32 "27" "27"  3 .   41965          .           .          .         . . ""
1963 32 "28" "28"  3 .   95698          .           .          .         . . ""
1963 32 "29" "29C" 3 .   69468          .           .          .         . . ""
1963 32 "30" "29C" 3 .       .          .           .          .         . . ""
1963 32 "31" "31A" 3 .   43261          .           .          .         . . ""
1963 32 "32" "31A" 3 .       .          .           .          .         . . ""
1963 32 "33" "33"  3 .    5366          .           .          .         . . ""
1963 32 "34" "34A" 3 .  173961          .           .          .         . . ""
1963 32 "35" "34A" 3 .       .          .           .          .         . . ""
1963 32 "36" "36"  3 .   42198          .           .          .         . . ""
1963 32 "37" "37"  0 .       .          .           .          .         . . ""
1963 32 "D"  "D"   3 . 1207674          .           .          .         . . ""
1963 36 "15" "15"  3 .  122000  288960046  2279200365  679840109  96320015 . ""
1963 36 "16" "16"  3 .    5000   11200002   133280021   44800007   4480001 . ""
1963 36 "17" "17"  3 .   76000  154560025   701120112  280000045  32480005 . ""
1963 36 "18" "18A" 3 .   99000  175840028   612640098  290080046  23520004 . ""
1963 36 "19" "18A" 3 .       .          .           .          .         . . ""
1963 36 "20" "20"  3 .   54000  123200020   487200078  212800034  15680003 . ""
1963 36 "21" "21"  3 .   27000   70560011   395360063  169120027  32480005 . ""
1963 36 "22" "22"  3 .   47000  122080020   386400062  221760035  24640004 . ""
1963 36 "23" "23"  3 .    8000   24640004   525280084  125440020  64960010 . ""
1963 36 "24" "24"  3 .   39000  105280017   744800119  331520053  43680007 . ""
1963 36 "25" "25"  3 .   31000   77280012   328160053  141120023  22400004 . ""
1963 36 "26" "26"  3 .   45000  118720019   478240077  239680038  57120009 . ""
1963 36 "27" "27"  3 .   87000  249760040  1562400250  507360081 152320024 . ""
1963 36 "28" "28"  3 .   63000  151200024   540960087  268800043  28000004 . ""
1963 36 "29" "29C" 3 .  102000  262080042   893760143  427840068  40320006 . ""
1963 36 "30" "29C" 3 .       .          .           .          .         . . ""
1963 36 "31" "31A" 3 .   72000  175840028   649600104  290080046  23520004 . ""
1963 36 "32" "31A" 3 .       .          .           .          .         . . ""
1963 36 "33" "33"  3 .    7000   15680003    48160008   26880004   6720001 . ""
1963 36 "34" "34A" 3 .  131000  344960055  1030400165  493920079  62720010 . ""
1963 36 "35" "34A" 3 .       .          .           .          .         . . ""
1963 36 "36" "36"  3 .   31000   69440011   256480041  123200020   8960001 . ""
1963 36 "37" "37"  0 .       .          .           .          .         . . ""
1963 36 "D"  "D"   3 . 1046000 2541280407 12053441929 4874240780 740320118 . ""
1963 40 "15" "15"  3 .   59988   96077045   767038159  239346089         . . ""
1963 40 "16" "16"  3 .    2698    4692262   140230665  114576707         . . ""
1963 40 "17" "17"  3 .   74700   87615520   473307669  170653625         . . ""
1963 40 "18" "18A" 3 .   53128   58923183   327153725  126115775         . . ""
1963 40 "19" "18A" 3 .       .          .           .          .         . . ""
1963 40 "20" "20"  3 .    8694    9923292    62461696   23615317         . . ""
1963 40 "21" "21"  3 .   24158   37038488   229461433   86231023         . . ""
1963 40 "22" "22"  3 .   21754   34807732   120961485   61077198         . . ""
1963 40 "23" "23"  3 .    3419    7423151   104884165   16038156         . . ""
1963 40 "24" "24"  3 .   34837   65615450   388230394  146923462         . . ""
1963 40 "25" "25"  3 .   10072   13846035    70076962   31384593         . . ""
1963 40 "26" "26"  3 .   45396   82923259   287923116  153307711         . . ""
1963 40 "27" "27"  3 .   75126  105653683   752307231  223730798         . . ""
1963 40 "28" "28"  3 .   44772   64769192   265769037  115038206         . . ""
1963 40 "29" "29C" 3 .   46247   96807928   295423007  117730990         . . ""
1963 40 "30" "29C" 3 .       .          .           .          .         . . ""
1963 40 "31" "31A" 3 .   59227   77922978   315461706  121038225         . . ""
1963 40 "32" "31A" 3 .       .          .           .          .         . . ""
1963 40 "33" "33"  3 .    8178   10307521    29077097   13230526         . . ""
1963 40 "34" "34A" 3 .   24962   37538621   156884859   64115578         . . ""
1963 40 "35" "34A" 3 .       .          .           .          .         . . ""
1963 40 "36" "36"  3 .   22721   27730705   148692190   66461709         . . ""
1963 40 "37" "37"  0 .       .          .           .          .         . . ""
1963 40 "D"  "D"   3 .  620077  919614987  4935345124 1890615159         . . ""
1963 56 "15" "15"  3 .  108000  170999977  2914399565  651600525  96800401 . ""
1963 56 "16" "16"  3 .    9000   12399675   262979762   25200328   3199760 . ""
1963 56 "17" "17"  3 .  148000  198200357  1204819326  350799698  66799627 . ""
1963 56 "18" "18A" 3 .   97000  106199595   561460239  204419961         . . ""
1963 56 "19" "20F" 3 .       .          .           .          .         . . ""
1963 56 "20" "20"  3 .   16100   23239809   109619611   27460168         . . ""
1963 56 "21" "21"  3 .   25000   42799814   298019796   85400349         . . ""
1963 56 "22" "22"  3 .   34000   61999987   323299996  123000352         . . ""
1963 56 "23" "23"  3 .   11000   31799934   840759907   69199850  14599812 . ""
1963 56 "24" "24"  3 .   61000  134999855   813219865  234199673  72399610 . ""
1963 56 "25" "25"  3 .   20100   32140402   191420029   66839967         . . ""
1963 56 "26" "26"  3 .   72000  116600025   469660370  217599809  47800347 . ""
1963 56 "27" "27"  3 .  124000  272600019  1383719467  314200125 187399755 . ""
1963 56 "28" "28"  3 .   60000  104799801  1064579320  182040198         . . ""
1963 56 "29" "29C" 3 .   70800  133839680   794740163  345340098         . . ""
1963 56 "30" "29C" 3 .       .          .           .          .         . . ""
1963 56 "31" "31A" 3 .  104900  193939658   781899976  325580007         . . ""
1963 56 "32" "31A" 3 .       .          .           .          .         . . ""
1963 56 "33" "33"  3 .    3700    6200241    44239948   13520317         . . ""
1963 56 "34" "34A" 3 .   57300  111020212   446580307  173719693         . . ""
1963 56 "35" "28F" 3 .       .          .           .          .         . . ""
1963 56 "36" "36"  3 .   43100   56619453   460320073  172079473         . . ""
1963 56 "37" "37"  0 .       .          .           .          .         . . ""
1963 56 "D"  "D"   3 . 1065000 1810399300 12965732072 3582198976 771399504 . ""
1963 76 "15" "15"  3 .  293000          .           .          .         . . ""
1963 76 "16" "16"  3 .   15000          .           .          .         . . ""
1963 76 "17" "17"  3 .  342000          .           .          .         . . ""
1963 76 "18" "18A" 3 .  108000          .           .          .         . . ""
end

[Clyde Schechter]

Clearly, the merge is not 1:1, 1:m or m:1.

On the contrary, based on the example data you show, this is most definitely an m:1 merge:

 * Example generated by -dataex-. To install: ssc install dataex (deflators)
clear
input int year float(fixedassetsdeflator cpi)
1963  30.315  30.633
1964  30.513  31.038
1965  30.853  31.528
1966  31.327  32.471
1967  32.093  33.375
1968  33.237  34.792
1969  34.638  36.683
1970  36.296  38.842
1971  37.997  40.483
1972  39.297  41.808
1973  40.882  44.425
1974  44.857  49.317
1975  50.766  53.825
1976  53.562  56.933
1977  57.111  60.617
1978   60.93  65.242
1979   65.83  72.583
1980  71.641  82.383
1981  78.453  90.933
1982  82.911  96.533
1983  82.774  99.583
1984  83.036 103.933
1985  83.893   107.6
1986  85.365 109.692
1987  86.339 113.617
1988  88.514 118.275
1989  90.572 123.942
1990  92.516 130.658
1991  94.267 136.167
1992   93.96 140.308
1993  94.161 144.475
1994  94.904 148.225
1995  95.849 152.383
1996  95.267 156.858
1997  94.735 160.525
1998  93.248 163.008
1999  92.314 166.583
2000  92.718 172.192
2001  92.346 177.042
2002  91.863 179.867
2003  91.156     184
2004  92.055 188.908
2005  94.443 195.267
2006  96.745 201.558
2007   98.31 207.344
2008  99.832 215.254
2009  99.184 214.565
2010  97.416 218.076
2011  98.559 224.923
2012     100 229.586
2013 100.251 232.952
2014 101.469 236.715
2015 101.909 237.002
2016 101.119 240.005
2017 101.977 245.121
2018 102.815 251.099
2019 104.137 255.647
   .       .       .
   .       .       .
end

drop if missing(year)
tempfile deflators
save `deflators'

 * Example generated by -dataex-. To install: ssc install dataex (industry level dataset)
clear
input int(year country) str2 isic str3 isiccomb byte sourcecode long(Establishments Employment) double(Wages OutputINDSTAT4 ValueAdded GrossFixed) long FemaleEmployees str16 value51
1963 32 "15" "15"  3 .  248175          .           .          .         . . ""
1963 32 "16" "16"  3 .    7663          .           .          .         . . ""
1963 32 "17" "17"  3 .  128391          .           .          .         . . ""
1963 32 "18" "18A" 3 .   80920          .           .          .         . . ""
1963 32 "19" "18A" 3 .       .          .           .          .         . . ""
1963 32 "20" "20"  3 .   40204          .           .          .         . . ""
1963 32 "21" "21"  3 .   21031          .           .          .         . . ""
1963 32 "22" "22"  3 .   40594          .           .          .         . . ""
1963 32 "23" "23"  3 .   10799          .           .          .         . . ""
1963 32 "24" "24"  3 .   62886          .           .          .         . . ""
1963 32 "25" "25"  3 .   25112          .           .          .         . . ""
1963 32 "26" "26"  3 .   69982          .           .          .         . . ""
1963 32 "27" "27"  3 .   41965          .           .          .         . . ""
1963 32 "28" "28"  3 .   95698          .           .          .         . . ""
1963 32 "29" "29C" 3 .   69468          .           .          .         . . ""
1963 32 "30" "29C" 3 .       .          .           .          .         . . ""
1963 32 "31" "31A" 3 .   43261          .           .          .         . . ""
1963 32 "32" "31A" 3 .       .          .           .          .         . . ""
1963 32 "33" "33"  3 .    5366          .           .          .         . . ""
1963 32 "34" "34A" 3 .  173961          .           .          .         . . ""
1963 32 "35" "34A" 3 .       .          .           .          .         . . ""
1963 32 "36" "36"  3 .   42198          .           .          .         . . ""
1963 32 "37" "37"  0 .       .          .           .          .         . . ""
1963 32 "D"  "D"   3 . 1207674          .           .          .         . . ""
1963 36 "15" "15"  3 .  122000  288960046  2279200365  679840109  96320015 . ""
1963 36 "16" "16"  3 .    5000   11200002   133280021   44800007   4480001 . ""
1963 36 "17" "17"  3 .   76000  154560025   701120112  280000045  32480005 . ""
1963 36 "18" "18A" 3 .   99000  175840028   612640098  290080046  23520004 . ""
1963 36 "19" "18A" 3 .       .          .           .          .         . . ""
1963 36 "20" "20"  3 .   54000  123200020   487200078  212800034  15680003 . ""
1963 36 "21" "21"  3 .   27000   70560011   395360063  169120027  32480005 . ""
1963 36 "22" "22"  3 .   47000  122080020   386400062  221760035  24640004 . ""
1963 36 "23" "23"  3 .    8000   24640004   525280084  125440020  64960010 . ""
1963 36 "24" "24"  3 .   39000  105280017   744800119  331520053  43680007 . ""
1963 36 "25" "25"  3 .   31000   77280012   328160053  141120023  22400004 . ""
1963 36 "26" "26"  3 .   45000  118720019   478240077  239680038  57120009 . ""
1963 36 "27" "27"  3 .   87000  249760040  1562400250  507360081 152320024 . ""
1963 36 "28" "28"  3 .   63000  151200024   540960087  268800043  28000004 . ""
1963 36 "29" "29C" 3 .  102000  262080042   893760143  427840068  40320006 . ""
1963 36 "30" "29C" 3 .       .          .           .          .         . . ""
1963 36 "31" "31A" 3 .   72000  175840028   649600104  290080046  23520004 . ""
1963 36 "32" "31A" 3 .       .          .           .          .         . . ""
1963 36 "33" "33"  3 .    7000   15680003    48160008   26880004   6720001 . ""
1963 36 "34" "34A" 3 .  131000  344960055  1030400165  493920079  62720010 . ""
1963 36 "35" "34A" 3 .       .          .           .          .         . . ""
1963 36 "36" "36"  3 .   31000   69440011   256480041  123200020   8960001 . ""
1963 36 "37" "37"  0 .       .          .           .          .         . . ""
1963 36 "D"  "D"   3 . 1046000 2541280407 12053441929 4874240780 740320118 . ""
1963 40 "15" "15"  3 .   59988   96077045   767038159  239346089         . . ""
1963 40 "16" "16"  3 .    2698    4692262   140230665  114576707         . . ""
1963 40 "17" "17"  3 .   74700   87615520   473307669  170653625         . . ""
1963 40 "18" "18A" 3 .   53128   58923183   327153725  126115775         . . ""
1963 40 "19" "18A" 3 .       .          .           .          .         . . ""
1963 40 "20" "20"  3 .    8694    9923292    62461696   23615317         . . ""
1963 40 "21" "21"  3 .   24158   37038488   229461433   86231023         . . ""
1963 40 "22" "22"  3 .   21754   34807732   120961485   61077198         . . ""
1963 40 "23" "23"  3 .    3419    7423151   104884165   16038156         . . ""
1963 40 "24" "24"  3 .   34837   65615450   388230394  146923462         . . ""
1963 40 "25" "25"  3 .   10072   13846035    70076962   31384593         . . ""
1963 40 "26" "26"  3 .   45396   82923259   287923116  153307711         . . ""
1963 40 "27" "27"  3 .   75126  105653683   752307231  223730798         . . ""
1963 40 "28" "28"  3 .   44772   64769192   265769037  115038206         . . ""
1963 40 "29" "29C" 3 .   46247   96807928   295423007  117730990         . . ""
1963 40 "30" "29C" 3 .       .          .           .          .         . . ""
1963 40 "31" "31A" 3 .   59227   77922978   315461706  121038225         . . ""
1963 40 "32" "31A" 3 .       .          .           .          .         . . ""
1963 40 "33" "33"  3 .    8178   10307521    29077097   13230526         . . ""
1963 40 "34" "34A" 3 .   24962   37538621   156884859   64115578         . . ""
1963 40 "35" "34A" 3 .       .          .           .          .         . . ""
1963 40 "36" "36"  3 .   22721   27730705   148692190   66461709         . . ""
1963 40 "37" "37"  0 .       .          .           .          .         . . ""
1963 40 "D"  "D"   3 .  620077  919614987  4935345124 1890615159         . . ""
1963 56 "15" "15"  3 .  108000  170999977  2914399565  651600525  96800401 . ""
1963 56 "16" "16"  3 .    9000   12399675   262979762   25200328   3199760 . ""
1963 56 "17" "17"  3 .  148000  198200357  1204819326  350799698  66799627 . ""
1963 56 "18" "18A" 3 .   97000  106199595   561460239  204419961         . . ""
1963 56 "19" "20F" 3 .       .          .           .          .         . . ""
1963 56 "20" "20"  3 .   16100   23239809   109619611   27460168         . . ""
1963 56 "21" "21"  3 .   25000   42799814   298019796   85400349         . . ""
1963 56 "22" "22"  3 .   34000   61999987   323299996  123000352         . . ""
1963 56 "23" "23"  3 .   11000   31799934   840759907   69199850  14599812 . ""
1963 56 "24" "24"  3 .   61000  134999855   813219865  234199673  72399610 . ""
1963 56 "25" "25"  3 .   20100   32140402   191420029   66839967         . . ""
1963 56 "26" "26"  3 .   72000  116600025   469660370  217599809  47800347 . ""
1963 56 "27" "27"  3 .  124000  272600019  1383719467  314200125 187399755 . ""
1963 56 "28" "28"  3 .   60000  104799801  1064579320  182040198         . . ""
1963 56 "29" "29C" 3 .   70800  133839680   794740163  345340098         . . ""
1963 56 "30" "29C" 3 .       .          .           .          .         . . ""
1963 56 "31" "31A" 3 .  104900  193939658   781899976  325580007         . . ""
1963 56 "32" "31A" 3 .       .          .           .          .         . . ""
1963 56 "33" "33"  3 .    3700    6200241    44239948   13520317         . . ""
1963 56 "34" "34A" 3 .   57300  111020212   446580307  173719693         . . ""
1963 56 "35" "28F" 3 .       .          .           .          .         . . ""
1963 56 "36" "36"  3 .   43100   56619453   460320073  172079473         . . ""
1963 56 "37" "37"  0 .       .          .           .          .         . . ""
1963 56 "D"  "D"   3 . 1065000 1810399300 12965732072 3582198976 771399504 . ""
1963 76 "15" "15"  3 .  293000          .           .          .         . . ""
1963 76 "16" "16"  3 .   15000          .           .          .         . . ""
1963 76 "17" "17"  3 .  342000          .           .          .         . . ""
1963 76 "18" "18A" 3 .  108000          .           .          .         . . ""
end

merge m:1 year using `deflators', keep(master match)

The only value of year that is not unique in the deflators data is a missing value. The rows with year missing also have missing values for the deflators themselves. They are just empty rows with no information. With tight data management, they wouldn't even be in that data set in the first place. If you drop those observations, as I have shown above, it's a simple m:1 merge using the deflator data set.

Now, however, something seems amiss. Your second data set has data from many countries, but the deflator data set makes no mention of country. It is hard for me to believe that there is a single set of deflators that applies across the globe. Surely inflation is different in different countries. At least, that's what I hear on the news. So I'm wondering if your real deflators data set also has a country variable. If it does, and if that's coded in the same way as the country variable in the second data set, then it's still an m:1 merge, this time with country year as the key, instead of just year.

[Hugo Rocha]

Thank you so much for your quick response! I have a set of countries. However, the values reported per country year and industry are in current US dollars. Ideally, I would find industry deflators but they are very incomplete and I would lose a lot of data. My idea is to have the same deflators per year. Would the merge still work? I want the same deflators per year. For instance, for year 1975 the same deflator applies to all industries and countries given that (again) reported values are in current US dollars

[Clyde Schechter]

Yes, then -merge m:1 year using deflator_data_set, keep(master match)- does it. Just remember to -drop- those observations in the deflator data set that have missing value for year.

[Hugo Rocha]

Thank you very much!