merging average value of a variable in using based on time interval indicated by variables in the master data

[Alberto Batinti]

Hello, I have two datasets.

I would like to merge the individuals in the first dataset with the average gdppc (gdp per capita) and leb (life expectancy at birth) in dataset 2 according to the individuals’ life span in dataset 1 and by country. For example, for individual #1 in "BEL" 224 1865 1790 - first dataset - I want to merge two variables with average values of leb and gdppc calculated for the interval 1790 1865 in the second dataset (country panel).

Thank you, Alberto

Dataset 1 – people ids
* Example generated by -dataex-. For more info, type help dataex
clear
input str3 iso3c int(id death_year birth_year)
"BEL" 224 1865 1790
"BEL" 225 1850 1812
"BEL" 226 1835 1778
"BEL" 227 1824 1779
"BEL" 228 1860 1781
"BEL" 229 1844 1784
"BEL" 230 1851 1785
"BEL" 231 1861 1786
"BEL" 232 1865 1790
"BEL" 233 1909 1835
"BEL" 234 1902 1836
"BEL" 235 1905 1837
"BEL" 236 1927 1840
"BEL" 237 1934 1875
"BEL" 238 1965 1876
"BEL" 239 1891 1869
"BEL" 240 1948 1870
"BEL" 241 1958 1872
"BEL" 242 1983 1901
"BEL" 243 1935 1905
"BEL" 244 2002 1916
"BEL" 245 1983 1903
"BEL" 246 2001 1906
"BEL" 247 1993 1930
"BEL" 248 2014 1928
"BEL" 249 2005 1927
"BEL" 250 . 1934
"BEL" 251 2009 1942
"BEL" 252 . 1951
"BEL" 253 . 1956
"BEL" 254 2022 1960
"DNK" 1 1766 1723
"DNK" 2 1751 1724
"DNK" 3 1796 1729
"DNK" 4 1756 1726
"DNK" 5 1808 1749
"DNK" 6 1775 1751
"DNK" 7 1813 1746
"DNK" 8 1820 1747
"DNK" 9 1831 1750
"DNK" 10 1839 1768
"DNK" 11 1852 1767
"DNK" 12 1843 1771
"DNK" 13 1848 1786
"DNK" 14 1881 1796
"DNK" 15 1850 1788
"DNK" 16 1864 1789
"DNK" 17 1863 1792
"DNK" 18 1863 1808
"DNK" 19 1874 1815
"DNK" 20 1906 1818
"DNK" 21 1898 1817
"DNK" 22 1869 1810
"DNK" 23 1902 1811
"DNK" 24 1878 1813
"DNK" 25 1885 1814
"DNK" 26 1893 1816
"DNK" 27 1894 1820
"DNK" 28 1903 1824
"DNK" 29 1911 1825
"DNK" 30 1849 1828
"DNK" 31 1912 1843
"DNK" 32 1926 1851
"DNK" 33 1925 1844
"DNK" 34 1913 1845
"DNK" 35 1928 1847
"DNK" 36 1933 1853
"DNK" 37 1939 1858
"DNK" 38 1947 1870
"DNK" 39 1952 1879
"DNK" 40 1957 1872
"DNK" 41 1906 1875
"DNK" 42 1949 1876
"DNK" 43 1958 1878
"DNK" 44 1945 1880
"DNK" 45 1944 1887
"DNK" 46 1961 1890
"DNK" 47 1972 1899
"DNK" 48 2000 1910
"DNK" 49 1976 1900
"DNK" 50 2022 1940
"ESP" 51 1759 1713
"ESP" 52 1758 1711
"ESP" 53 1724 1707
"ESP" 54 1788 1716
"ESP" 55 1760 1724
"ESP" 56 1781 1718
"ESP" 57 1765 1720
"ESP" 58 1746 1726
"ESP" 59 1785 1727
"ESP" 60 1785 1729
"ESP" 61 1819 1748
"ESP" 62 1819 1751
"ESP" 63 1742 1740
"ESP" 64 1742 1742
"ESP" 65 1749 1743
"ESP" 66 1801 1744
"ESP" 67 1792 1745
"ESP" 68 1777 1747
"ESP" 69 1819 1748
end
[/CODE]

Dataset 2 – country panel leb & gdppc

[

* Example generated by -dataex-. For more info, type help dataex
clear
input str8 iso3c int year double(gdp_pc_maddison leb_pop)
"DNK" 1833      2130      .
"BEL" 1916      6503      .
"ESP" 1911      3027   39.7
"BEL" 1928      8191  57.44
"ESP" 1951      3790 63.488
"ESP" 1964      6945 70.416
"BEL" 1995 29370.088 76.907
"ESP" 2015     31847 82.981
"BEL" 1926      7626  56.89
"ESP" 1929      4173  49.43
"DNK" 1912      6076  58.01
"ESP" 1858      1774      .
"ESP" 1906      2775      .
"DNK" 1998  36361.23 76.245
"ESP" 1873      2428      .
"ESP" 1867      1946      .
"DNK" 1930      8513  62.27
"DNK" 1991 29915.447 75.045
"DNK" 1825      2107      .
"ESP" 1785   1447.47      .
"BEL" 1820      2358      .
"DNK" 1967     18230 72.867
"BEL" 1932      7343   58.5
"ESP" 1954      4328  65.67
"ESP" 1923      3433  44.69
"BEL" 1924      7393  57.44
"DNK" 1909      5807  57.37
"ESP" 1856      1800      .
"DNK" 1881      3480  48.26
"ESP" 1897      2469      .
"BEL" 1973     19399 71.598
"BEL" 1891      5412  43.99
"ESP" 1931      3787  49.16
"BEL" 1868      4095  42.53
"DNK" 1933      8434  63.61
"DNK" 1888      3808  46.66
"BEL" 1966     13989  70.59
"BEL" 1864      3931  39.76
"BEL" 1848      2796  38.03
"BEL" 1967     14461 70.679
"ESP" 1973     11638 72.842
"DNK" 2003 39983.145 77.439
"DNK" 1946      9208  67.21
"DNK" 1821      2104      .
"BEL" 1919      5402  49.79
"BEL" 1925      7438  57.09
"ESP" 1961      5587 69.371
"BEL" 1999 32571.674 77.672
"BEL" 1969     15969 70.903
"ESP" 1835   1679.78      .
"ESP" 1971      9894 72.261
"ESP" 1925      3725  46.88
"DNK" 1944      8835  66.38
"DNK" 1948      9776  70.11
"ESP" 1895      2589      .
"ESP" 1805   1501.08      .
"BEL" 1949      8278  65.43
"DNK" 2005  42264.63 77.936
"ESP" 1898      2628      .
"ESP" 1903      2713      .
"BEL" 1842         .  38.64
"DNK" 1861      2785  47.59
"ESP" 1960      5037 68.958
"ESP" 1980     14008 75.332
"BEL" 1914      6253      .
"BEL" 1984     23644 74.327
"DNK" 1956     11858 71.851
"DNK" 1951     11056 70.629
"BEL" 1858      3526  40.13
"BEL" 1920      6315  53.59
"DNK" 1863      2979  47.52
"BEL" 1959     10533 69.796
"ESP" 1894      2633      .
"BEL" 1970     16914 71.046
"ESP" 1862      1932      .
"DNK" 1832      2158      .
"BEL" 1873      4495  43.46
"BEL" 1962     12087 70.241
"BEL" 1812      2358      .
"BEL" 1909      6330  50.63
"ESP" 1870      1809      .
"DNK" 1853      2697  38.52
"ESP" 1908      2917  41.33
"DNK" 1925      6978  61.91
"DNK" 2013     43733 80.171
"ESP" 1992 20329.168 77.552
"ESP" 1948      3478  61.22
"DNK" 1919      6153     57
"ESP" 1962      6083 69.749
"DNK" 1903      5244  54.75
"DNK" 1980     24272 74.363
"BEL" 1878      4689  44.19
"BEL" 2016     38766 81.139
"BEL" 1897      5716   48.8
"BEL" 1990     27412 75.862
"DNK" 1830      2120      .
"DNK" 2002  39709.37 77.195
"BEL" 1955     10010 68.759
"ESP" 1953      4066 64.983
"BEL" 1983     23071 74.056
"DNK" 1868      2954  45.61
"DNK" 1826      2110      .
"ESP" 1854      1804      .
"DNK" 1882      3571  46.67
"ESP" 1919      3024  41.06
"ESP" 1946      3473   57.5
"DNK" 1896      4521  52.89
"BEL" 1907      6268  51.03
"BEL" 2009  36998.65 79.873
"ESP" 1975     12621 73.503
"DNK" 1870      3193  45.97
"DNK" 1995  33356.14 75.629
"ESP" 1845   1733.39      .
"ESP" 1955      4442 66.318
"BEL" 1852      3067  41.78
"ESP" 1861      1932      .
"ESP" 1956      4769 66.925
"DNK" 1981     24063 74.402
"ESP" 1850      1706      .
"BEL" 1889      5386  45.02
"DNK" 1971     20617 73.414
"BEL" 1900      5947  46.52
"BEL" 1929      8056  55.01
"DNK" 1775         .     33
"DNK" 1913      6236  58.87
"BEL" 1921      6465  54.67
"DNK" 1901      4948  52.59
"BEL" 2015     38569 80.969
"DNK" 1923      7267  61.17
"ESP" 1993  20056.54 77.745
"BEL" 2011     38130 80.261
"BEL" 1936      7831  59.94
"ESP" 1914      2962  42.75
"DNK" 1961     14843  72.34
"ESP" 1999  25549.54   79.1
"ESP" 1875      2276      .
"DNK" 2007  44481.47  78.49
"DNK" 1872      3327  47.41
"ESP" 1995 21522.098 78.175
"BEL" 1972     18336 71.397
"DNK" 1865      2989  39.78
"BEL" 1877      4597  44.45
"DNK" 2004 41178.562 77.684
"DNK" 1834      2227      .
"DNK" 1958     12903 72.111
"BEL" 1883      5013  44.86
"BEL" 1750      2169      .
"ESP" 1815   1536.82      .
"BEL" 1911      6612  49.22
"DNK" 1875      3366  44.44
"DNK" 1952     11086 70.943
"DNK" 1843      2353  43.86
"DNK" 1986     28681 74.632
"ESP" 1863      1959      .
"BEL" 1960     11081 69.973
"ESP" 1969      9307 71.746
"ESP" 1959      5085 68.509
"ESP" 1966      7881 70.991
"DNK" 1926      7329  61.67
"BEL" 1979     22094 73.015
"BEL" 1994   28706.5 76.709
"DNK" 1993 30649.537 75.303
"BEL" 1899      5828  46.45
"DNK" 2016     44836 80.583
"DNK" 1966     17789 72.747
"ESP" 1982     14045 75.934
"ESP" 1974     12388 73.162
"DNK" 1931      8542  61.84
"ESP" 1725    1429.6      .
"DNK" 1894      4235  49.36
"BEL" 2018 39756.203 81.468
"BEL" 1908      6269  49.63
"BEL" 1927      7847  57.11
"ESP" 2012     30699 82.384
"ESP" 1878      2495      .
"DNK" 1928      7627  61.87
"ESP" 1745   1375.99      .
"BEL" 1896      5660  48.04
"DNK" 1976     23059  74.08
"BEL" 1849      2853  34.49
"DNK" 1918      5514  56.28
"ESP" 1958      5176  68.02
"DNK" 1857      2745  42.45
"BEL" 1922      7034   55.1
"BEL" 1963     12532 70.341
"DNK" 1910      5906  58.03
"BEL" 1865      3902  37.69
"ESP" 2005 30885.602 80.553
"DNK" 1860      2775  45.05
"DNK" 1855      2938  46.13
"ESP" 1967      8215 71.252
"BEL" 2012     37906 80.444
"DNK" 1947      9620  68.53
"BEL" 1918      4560      .
"BEL" 1700      2192      .
"ESP" 1865      1865      .
"BEL" 1847      2809   35.1
"DNK" 1938      9185  64.98
"BEL" 1893      5507  44.39
"BEL" 1905      6188  49.92
"ESP" 1905      2622      .
"ESP" 1889      2469      .
"DNK" 1985     27710 74.573
"ESP" 1985     14690 76.553
"DNK" 1987     28728 74.698
"ESP" 1926      3650   47.7
"ESP" 1877      2601      .
"DNK" 1831      2101      .
"DNK" 1893      4191  47.05
"DNK" 1848      2570  40.76
"DNK" 1874      3341  46.01
"BEL" 1993  27857.25 76.507
"ESP" 1868      1701      .
"BEL" 1855      3301  37.42
"ESP" 1885      2493      .
"ESP" 1909      2978  40.96
"BEL" 1974     20153 71.813
"BEL" 1941      6947  58.89
"ESP" 1937      2654  47.28
"DNK" 1937      9035  63.95
"BEL" 2010  37739.33 80.072
"DNK" 2015     44635 80.475
"BEL" 1857      3524  40.44
"ESP" 1950      3464 63.094
"DNK" 1905      5333  54.31
"BEL" 2002 34419.695 78.285
"DNK" 1841      2254  42.62
"DNK" 1970     20221 73.271
"DNK" 1891      4073  46.75
"ESP" 1957      4900 67.492
"DNK" 1828      2163      .
"BEL" 1978     21605 72.764
"DNK" 1988     29049  74.77
"ESP" 1886      2436      .
"BEL" 1923      7226  56.69
"BEL" 1915      6150      .
"DNK" 1929      8089  61.93
"BEL" 2004  35740.35  78.74
"DNK" 1883      3665  48.48
"BEL" 1997 30894.283 77.292
"BEL" 1961     11561 70.121
"ESP" 2010 31786.404   81.9
"DNK" 1953     11623 71.222
"BEL" 1939      8209  60.01
"BEL" 1977     21025 72.517
"DNK" 2000 39021.176 76.712
"ESP" 1979     13681 74.982
"DNK" 1869      3092  46.08
"ESP" 1852      1785      .
"ESP" 1869      1760      .
"BEL" 1863      3832  41.25
"ESP" 1939      2885  47.21
"ESP" 1942      3394  52.48
"ESP" 2003 29371.865 80.041
"ESP" 1892      2719   32.1
"ESP" 1888      2475      .
"BEL" 1964     13295 70.427
"DNK" 1900      4809  51.87
"DNK" 2011     43575 79.704
"ESP" 2009 31669.691 81.638
"ESP" 1795   1518.95      .
"ESP" 1932      3856  51.12
"DNK" 1852      2713     43
"ESP" 1755    1429.6      .
"ESP" 1996 22185.066 78.405
"DNK" 2009  42090.17 79.107
"DNK" 2010   42932.4 79.416
"ESP" 1921      3322  41.95
"DNK" 1983     25450 74.477
"BEL" 1938      7702  60.12
"BEL" 1874      4607  44.65
"BEL" 1870      4291   40.9
"ESP" 1928      3907  48.61
"ESP" 1986     15237 76.696
"ESP" 1983     14237 76.178
"DNK" 1878      3351  47.54
"DNK" 1897      4564  51.77
"ESP" 2014     30819 82.801
"ESP" 1904      2660      .
"BEL" 1965     13643 70.507
"ESP" 2016     30110 83.145
"ESP" 1915      2973  42.97
"ESP" 2011     31600 82.149
"ESP" 1944      3676  56.22
"DNK" 2006 44025.484 78.204
end

Hello, I have two datasets.

I would like to merge the individuals’ in the first dataset with the average gdppc (gdp per capita) and leb (life expectancy at birth) in dataset 2 according to the individuals’ life span in dataset 1.

For example for individual #1 in "BEL" 224 1865 1790 in first I want to add two variables with average values of leb and gdppc calculated for the interval 1790 1865.

[Ken Chui]

Check out help rangejoin, here is an example:

clear
input str8 iso3c int year double(gdp_pc_maddison leb_pop)
"DNK" 1833      2130      .
"BEL" 1916      6503      .
"ESP" 1911      3027   39.7
"BEL" 1928      8191  57.44
"ESP" 1951      3790 63.488
"ESP" 1964      6945 70.416
"BEL" 1995 29370.088 76.907
"ESP" 2015     31847 82.981
"BEL" 1926      7626  56.89
"ESP" 1929      4173  49.43
"DNK" 1912      6076  58.01
"ESP" 1858      1774      .
"ESP" 1906      2775      .
"DNK" 1998  36361.23 76.245
"ESP" 1873      2428      .
"ESP" 1867      1946      .
"DNK" 1930      8513  62.27
"DNK" 1991 29915.447 75.045
"DNK" 1825      2107      .
"ESP" 1785   1447.47      .
"BEL" 1820      2358      .
"DNK" 1967     18230 72.867
"BEL" 1932      7343   58.5
"ESP" 1954      4328  65.67
"ESP" 1923      3433  44.69
"BEL" 1924      7393  57.44
"DNK" 1909      5807  57.37
"ESP" 1856      1800      .
"DNK" 1881      3480  48.26
"ESP" 1897      2469      .
"BEL" 1973     19399 71.598
"BEL" 1891      5412  43.99
"ESP" 1931      3787  49.16
"BEL" 1868      4095  42.53
"DNK" 1933      8434  63.61
"DNK" 1888      3808  46.66
"BEL" 1966     13989  70.59
"BEL" 1864      3931  39.76
"BEL" 1848      2796  38.03
"BEL" 1967     14461 70.679
"ESP" 1973     11638 72.842
"DNK" 2003 39983.145 77.439
"DNK" 1946      9208  67.21
"DNK" 1821      2104      .
"BEL" 1919      5402  49.79
"BEL" 1925      7438  57.09
"ESP" 1961      5587 69.371
"BEL" 1999 32571.674 77.672
"BEL" 1969     15969 70.903
"ESP" 1835   1679.78      .
"ESP" 1971      9894 72.261
"ESP" 1925      3725  46.88
"DNK" 1944      8835  66.38
"DNK" 1948      9776  70.11
"ESP" 1895      2589      .
"ESP" 1805   1501.08      .
"BEL" 1949      8278  65.43
"DNK" 2005  42264.63 77.936
"ESP" 1898      2628      .
"ESP" 1903      2713      .
"BEL" 1842         .  38.64
"DNK" 1861      2785  47.59
"ESP" 1960      5037 68.958
"ESP" 1980     14008 75.332
"BEL" 1914      6253      .
"BEL" 1984     23644 74.327
"DNK" 1956     11858 71.851
"DNK" 1951     11056 70.629
"BEL" 1858      3526  40.13
"BEL" 1920      6315  53.59
"DNK" 1863      2979  47.52
"BEL" 1959     10533 69.796
"ESP" 1894      2633      .
"BEL" 1970     16914 71.046
"ESP" 1862      1932      .
"DNK" 1832      2158      .
"BEL" 1873      4495  43.46
"BEL" 1962     12087 70.241
"BEL" 1812      2358      .
"BEL" 1909      6330  50.63
"ESP" 1870      1809      .
"DNK" 1853      2697  38.52
"ESP" 1908      2917  41.33
"DNK" 1925      6978  61.91
"DNK" 2013     43733 80.171
"ESP" 1992 20329.168 77.552
"ESP" 1948      3478  61.22
"DNK" 1919      6153     57
"ESP" 1962      6083 69.749
"DNK" 1903      5244  54.75
"DNK" 1980     24272 74.363
"BEL" 1878      4689  44.19
"BEL" 2016     38766 81.139
"BEL" 1897      5716   48.8
"BEL" 1990     27412 75.862
"DNK" 1830      2120      .
"DNK" 2002  39709.37 77.195
"BEL" 1955     10010 68.759
"ESP" 1953      4066 64.983
"BEL" 1983     23071 74.056
"DNK" 1868      2954  45.61
"DNK" 1826      2110      .
"ESP" 1854      1804      .
"DNK" 1882      3571  46.67
"ESP" 1919      3024  41.06
"ESP" 1946      3473   57.5
"DNK" 1896      4521  52.89
"BEL" 1907      6268  51.03
"BEL" 2009  36998.65 79.873
"ESP" 1975     12621 73.503
"DNK" 1870      3193  45.97
"DNK" 1995  33356.14 75.629
"ESP" 1845   1733.39      .
"ESP" 1955      4442 66.318
"BEL" 1852      3067  41.78
"ESP" 1861      1932      .
"ESP" 1956      4769 66.925
"DNK" 1981     24063 74.402
"ESP" 1850      1706      .
"BEL" 1889      5386  45.02
"DNK" 1971     20617 73.414
"BEL" 1900      5947  46.52
"BEL" 1929      8056  55.01
"DNK" 1775         .     33
"DNK" 1913      6236  58.87
"BEL" 1921      6465  54.67
"DNK" 1901      4948  52.59
"BEL" 2015     38569 80.969
"DNK" 1923      7267  61.17
"ESP" 1993  20056.54 77.745
"BEL" 2011     38130 80.261
"BEL" 1936      7831  59.94
"ESP" 1914      2962  42.75
"DNK" 1961     14843  72.34
"ESP" 1999  25549.54   79.1
"ESP" 1875      2276      .
"DNK" 2007  44481.47  78.49
"DNK" 1872      3327  47.41
"ESP" 1995 21522.098 78.175
"BEL" 1972     18336 71.397
"DNK" 1865      2989  39.78
"BEL" 1877      4597  44.45
"DNK" 2004 41178.562 77.684
"DNK" 1834      2227      .
"DNK" 1958     12903 72.111
"BEL" 1883      5013  44.86
"BEL" 1750      2169      .
"ESP" 1815   1536.82      .
"BEL" 1911      6612  49.22
"DNK" 1875      3366  44.44
"DNK" 1952     11086 70.943
"DNK" 1843      2353  43.86
"DNK" 1986     28681 74.632
"ESP" 1863      1959      .
"BEL" 1960     11081 69.973
"ESP" 1969      9307 71.746
"ESP" 1959      5085 68.509
"ESP" 1966      7881 70.991
"DNK" 1926      7329  61.67
"BEL" 1979     22094 73.015
"BEL" 1994   28706.5 76.709
"DNK" 1993 30649.537 75.303
"BEL" 1899      5828  46.45
"DNK" 2016     44836 80.583
"DNK" 1966     17789 72.747
"ESP" 1982     14045 75.934
"ESP" 1974     12388 73.162
"DNK" 1931      8542  61.84
"ESP" 1725    1429.6      .
"DNK" 1894      4235  49.36
"BEL" 2018 39756.203 81.468
"BEL" 1908      6269  49.63
"BEL" 1927      7847  57.11
"ESP" 2012     30699 82.384
"ESP" 1878      2495      .
"DNK" 1928      7627  61.87
"ESP" 1745   1375.99      .
"BEL" 1896      5660  48.04
"DNK" 1976     23059  74.08
"BEL" 1849      2853  34.49
"DNK" 1918      5514  56.28
"ESP" 1958      5176  68.02
"DNK" 1857      2745  42.45
"BEL" 1922      7034   55.1
"BEL" 1963     12532 70.341
"DNK" 1910      5906  58.03
"BEL" 1865      3902  37.69
"ESP" 2005 30885.602 80.553
"DNK" 1860      2775  45.05
"DNK" 1855      2938  46.13
"ESP" 1967      8215 71.252
"BEL" 2012     37906 80.444
"DNK" 1947      9620  68.53
"BEL" 1918      4560      .
"BEL" 1700      2192      .
"ESP" 1865      1865      .
"BEL" 1847      2809   35.1
"DNK" 1938      9185  64.98
"BEL" 1893      5507  44.39
"BEL" 1905      6188  49.92
"ESP" 1905      2622      .
"ESP" 1889      2469      .
"DNK" 1985     27710 74.573
"ESP" 1985     14690 76.553
"DNK" 1987     28728 74.698
"ESP" 1926      3650   47.7
"ESP" 1877      2601      .
"DNK" 1831      2101      .
"DNK" 1893      4191  47.05
"DNK" 1848      2570  40.76
"DNK" 1874      3341  46.01
"BEL" 1993  27857.25 76.507
"ESP" 1868      1701      .
"BEL" 1855      3301  37.42
"ESP" 1885      2493      .
"ESP" 1909      2978  40.96
"BEL" 1974     20153 71.813
"BEL" 1941      6947  58.89
"ESP" 1937      2654  47.28
"DNK" 1937      9035  63.95
"BEL" 2010  37739.33 80.072
"DNK" 2015     44635 80.475
"BEL" 1857      3524  40.44
"ESP" 1950      3464 63.094
"DNK" 1905      5333  54.31
"BEL" 2002 34419.695 78.285
"DNK" 1841      2254  42.62
"DNK" 1970     20221 73.271
"DNK" 1891      4073  46.75
"ESP" 1957      4900 67.492
"DNK" 1828      2163      .
"BEL" 1978     21605 72.764
"DNK" 1988     29049  74.77
"ESP" 1886      2436      .
"BEL" 1923      7226  56.69
"BEL" 1915      6150      .
"DNK" 1929      8089  61.93
"BEL" 2004  35740.35  78.74
"DNK" 1883      3665  48.48
"BEL" 1997 30894.283 77.292
"BEL" 1961     11561 70.121
"ESP" 2010 31786.404   81.9
"DNK" 1953     11623 71.222
"BEL" 1939      8209  60.01
"BEL" 1977     21025 72.517
"DNK" 2000 39021.176 76.712
"ESP" 1979     13681 74.982
"DNK" 1869      3092  46.08
"ESP" 1852      1785      .
"ESP" 1869      1760      .
"BEL" 1863      3832  41.25
"ESP" 1939      2885  47.21
"ESP" 1942      3394  52.48
"ESP" 2003 29371.865 80.041
"ESP" 1892      2719   32.1
"ESP" 1888      2475      .
"BEL" 1964     13295 70.427
"DNK" 1900      4809  51.87
"DNK" 2011     43575 79.704
"ESP" 2009 31669.691 81.638
"ESP" 1795   1518.95      .
"ESP" 1932      3856  51.12
"DNK" 1852      2713     43
"ESP" 1755    1429.6      .
"ESP" 1996 22185.066 78.405
"DNK" 2009  42090.17 79.107
"DNK" 2010   42932.4 79.416
"ESP" 1921      3322  41.95
"DNK" 1983     25450 74.477
"BEL" 1938      7702  60.12
"BEL" 1874      4607  44.65
"BEL" 1870      4291   40.9
"ESP" 1928      3907  48.61
"ESP" 1986     15237 76.696
"ESP" 1983     14237 76.178
"DNK" 1878      3351  47.54
"DNK" 1897      4564  51.77
"ESP" 2014     30819 82.801
"ESP" 1904      2660      .
"BEL" 1965     13643 70.507
"ESP" 2016     30110 83.145
"ESP" 1915      2973  42.97
"ESP" 2011     31600 82.149
"ESP" 1944      3676  56.22
"DNK" 2006 44025.484 78.204
end
save file02, replace

clear
input str3 iso3c int(id death_year birth_year)
"BEL" 224 1865 1790
"BEL" 225 1850 1812
"DNK" 32 1926 1851
"DNK" 33 1925 1844
"DNK" 34 1913 1845
"ESP" 57 1765 1720
"ESP" 58 1746 1726
"ESP" 59 1785 1727
end

drop if missing(death_year, birth_year)
rangejoin year birth_year death_year using file02, by(iso3c)
collapse (mean) gdp_pc_maddison leb_pop, by(iso3c id)
list, sep(0)

Results:

     +-------------------------------------+
     | iso3c    id   gdp_pc_~n     leb_pop |
     |-------------------------------------|
  1. |   BEL   224   3188.0833   38.611818 |
  2. |   BEL   225      2634.8      36.565 |
  3. |   DNK    32    4296.027    50.13027 |
  4. |   DNK    33   4167.4054   49.565135 |
  5. |   DNK    34   3887.3333   48.410606 |
  6. |   ESP    57     1411.73           . |
  7. |   ESP    58     1375.99           . |
  8. |   ESP    59   1417.6867           . |
     +-------------------------------------+

Notice that to prevent wrong merges, I deleted people without complete birth/death dates. You may need to merge them back.

[Alberto Batinti]

Thank you very much, it really looks like rangejoin the command I needed to do this.
All the best, A