solving fraction

[Paris Rira]

Dear Profs and Colleagues,

I need to compute this fraction:




IMM: immigrant share from origin country O during the period t: 2010-2019.
Native d, t-1: Native population in 7 regions (d) during t-1.

Numerator:

* Example generated by -dataex-. For more info, type help dataex
clear
input str24 country int year long IMM
"Spain"                    2010   8918
"France"                   2010   5111
"Italy"                    2010   5067
"United Kingdom"           2010  17196
"Ukraine"                  2010  49487
"Romania"                  2010  36830
"Moldavia"                 2010  15632
"Other European countries" 2010  38593
"Angola"                   2010  23233
"Cape Verde"               2010  43510
"Guinea Bissau"            2010  19304
"Mozambique"               2010   3109
"Sao Tome and Principe"    2010  10175
"Other African countries"  2010   7748
"Brazil"                   2010 119195
"Other American countries" 2010   8677
"China"                    2010  15600
"India"                    2010   5213
"Nepal"                    2010    796
"Other Asian countries"    2010   9352
"Spain"                    2011   9310
"France"                   2011   5293
"Italy"                    2011   5338
"United Kingdom"           2011  17675
"Ukraine"                  2011  48010
"Romania"                  2011  39312
"Moldavia"                 2011  13586
"Other European countries" 2011  39004
"Angola"                   2011  21329
"Cape Verde"               2011  43475
"Guinea Bissau"            2011  18131
"Mozambique"               2011   2995
"Sao Tome and Principe"    2011  10274
"Other African countries"  2011   7789
"Brazil"                   2011 111295
"Other American countries" 2011   8877
"China"                    2011  16595
"India"                    2011   5316
"Nepal"                    2011   1144
"Other Asian countries"    2011   9645
"Spain"                    2012   9216
"France"                   2012   5201
"Italy"                    2012   5222
"United Kingdom"           2012  16649
"Ukraine"                  2012  44050
"Romania"                  2012  35216
"Moldavia"                 2012  11503
"Other European countries" 2012  37023
"Angola"                   2012  19873
"Cape Verde"               2012  42388
"Guinea Bissau"            2012  17462
"Mozambique"               2012   2901
"Sao Tome and Principe"    2012  10174
"Other African countries"  2012   8078
"Brazil"                   2012 105518
"Other American countries" 2012   9022
"China"                    2012  17186
"India"                    2012   5574
"Nepal"                    2012   1702
"Other Asian countries"    2012  10200
"Spain"                    2013   9541
"France"                   2013   5268
"Italy"                    2013   5121
"United Kingdom"           2013  16471
"Ukraine"                  2013  41074
"Romania"                  2013  34204
"Moldavia"                 2013   9968
"Other European countries" 2013  37345
"Angola"                   2013  19967
"Cape Verde"               2013  42011
"Guinea Bissau"            2013  17574
"Mozambique"               2013   2825
"Sao Tome and Principe"    2013  10169
"Other African countries"  2013   8299
"Brazil"                   2013  91238
"Other American countries" 2013   9058
"China"                    2013  18445
"India"                    2013   5983
"Nepal"                    2013   2551
"Other Asian countries"    2013  10826
"Spain"                    2014   9692
"France"                   2014   6541
"Italy"                    2014   5328
"United Kingdom"           2014  16559
"Ukraine"                  2014  37809
"Romania"                  2014  31505
"Moldavia"                 2014   8458
"Other European countries" 2014  38044
"Angola"                   2014  19478
"Cape Verde"               2014  40563
"Guinea Bissau"            2014  17728
"Mozambique"               2014   2813
"Sao Tome and Principe"    2014  10028
"Other African countries"  2014   8338
"Brazil"                   2014  85288
"Other American countries" 2014   9104
"China"                    2014  21042
"India"                    2014   6372
"Nepal"                    2014   3543
"Other Asian countries"    2014  11535
end

Denominator:

* Example generated by -dataex-. For more info, type help dataex
clear
input int D byte region long local_pop
2009 1 3660027
2009 2 2276922
2009 3 2569821
2009 4  737925
2009 5  369714
2009 6  243259
2009 7  258837
2010 1 3650003
2010 2 2271508
2010 3 2594130
2010 4  733699
2010 5  376756
2010 6  243375
2010 7  260576
2011 1 3641191
2011 2 2267769
2011 3 2605266
2011 4  729568
2011 5  382890
2011 6  243535
2011 7  260910
2012 1 3628217
2012 2 2260102
2012 3 2610494
2012 4  725674
2012 5  389095
2012 6  243429
2012 7  259801
2013 1 3607664
2013 2 2244571
2013 3 2612571
2013 4  719157
2013 5  391772
2013 6  242591
2013 7  257398
2014 1 3586851
2014 2 2227815
2014 3 2614567
2014 4  712295
2014 5  392933
2014 6  241024
2014 7  254011
2015 1 3570236
2015 2 2213110
2015 3 2626870
2015 4  705638
2015 5  392712
2015 6  239178
2015 7  250335
2016 1 3556744
2016 2 2197865
2016 3 2635958
2016 4  698893
2016 5  389192
2016 6  237529
2016 7  247368
2017 1 3545137
2017 2 2182753
2017 3 2636948
2017 4  691623
2017 5  385887
2017 6  236093
2017 7  245003
2018 1 3532359
2018 2 2168704
2018 3 2613291
2018 4  684337
2018 5  380445
2018 6  234825
2018 7  243202
2019 1 3518708
2019 2 2150940
2019 3 2573936
2019 4  676251
2019 5  370046
2019 6  233614
2019 7  241976

end

How can I merge data and compute this fraction? Any ideas are appreciated.
Cheers,
Paris

************************************************** ***************************************
And we are honored to listen to David Card in Lisbon, Portugal this week

[Clyde Schechter]

It is impossible to combine these two data sets directly as there are no shared variables that identify observations. However, it may be possible to do so indirectly if there is some other file that crosswalks region from the second data set with country in the first data set. Do you have any such file available?

[Ken Chui]

May need some cross-walk file or table like this one: https://www.ucl.ac.uk/global/regiona...ions-directory. And to do that, it would be necessary to check the source of your second data set to find out the code of the 7 regions.

[Hemanshu Kumar]

I am going to assume that the fraction you listed makes sense for your purposes, i.e. that you want to divide each origin country's immigrants (which are NOT region-specific -- they are not indexed by d in your formula), by the native population of each of seven regions in the previous year.

If so, perhaps this is what you are looking for:

clear
input str24 country int year long IMM
"Spain"                    2010   8918
"France"                   2010   5111
"Italy"                    2010   5067
"United Kingdom"           2010  17196
"Ukraine"                  2010  49487
"Romania"                  2010  36830
"Moldavia"                 2010  15632
"Other European countries" 2010  38593
"Angola"                   2010  23233
"Cape Verde"               2010  43510
"Guinea Bissau"            2010  19304
"Mozambique"               2010   3109
"Sao Tome and Principe"    2010  10175
"Other African countries"  2010   7748
"Brazil"                   2010 119195
"Other American countries" 2010   8677
"China"                    2010  15600
"India"                    2010   5213
"Nepal"                    2010    796
"Other Asian countries"    2010   9352
"Spain"                    2011   9310
"France"                   2011   5293
"Italy"                    2011   5338
"United Kingdom"           2011  17675
"Ukraine"                  2011  48010
"Romania"                  2011  39312
"Moldavia"                 2011  13586
"Other European countries" 2011  39004
"Angola"                   2011  21329
"Cape Verde"               2011  43475
"Guinea Bissau"            2011  18131
"Mozambique"               2011   2995
"Sao Tome and Principe"    2011  10274
"Other African countries"  2011   7789
"Brazil"                   2011 111295
"Other American countries" 2011   8877
"China"                    2011  16595
"India"                    2011   5316
"Nepal"                    2011   1144
"Other Asian countries"    2011   9645
"Spain"                    2012   9216
"France"                   2012   5201
"Italy"                    2012   5222
"United Kingdom"           2012  16649
"Ukraine"                  2012  44050
"Romania"                  2012  35216
"Moldavia"                 2012  11503
"Other European countries" 2012  37023
"Angola"                   2012  19873
"Cape Verde"               2012  42388
"Guinea Bissau"            2012  17462
"Mozambique"               2012   2901
"Sao Tome and Principe"    2012  10174
"Other African countries"  2012   8078
"Brazil"                   2012 105518
"Other American countries" 2012   9022
"China"                    2012  17186
"India"                    2012   5574
"Nepal"                    2012   1702
"Other Asian countries"    2012  10200
"Spain"                    2013   9541
"France"                   2013   5268
"Italy"                    2013   5121
"United Kingdom"           2013  16471
"Ukraine"                  2013  41074
"Romania"                  2013  34204
"Moldavia"                 2013   9968
"Other European countries" 2013  37345
"Angola"                   2013  19967
"Cape Verde"               2013  42011
"Guinea Bissau"            2013  17574
"Mozambique"               2013   2825
"Sao Tome and Principe"    2013  10169
"Other African countries"  2013   8299
"Brazil"                   2013  91238
"Other American countries" 2013   9058
"China"                    2013  18445
"India"                    2013   5983
"Nepal"                    2013   2551
"Other Asian countries"    2013  10826
"Spain"                    2014   9692
"France"                   2014   6541
"Italy"                    2014   5328
"United Kingdom"           2014  16559
"Ukraine"                  2014  37809
"Romania"                  2014  31505
"Moldavia"                 2014   8458
"Other European countries" 2014  38044
"Angola"                   2014  19478
"Cape Verde"               2014  40563
"Guinea Bissau"            2014  17728
"Mozambique"               2014   2813
"Sao Tome and Principe"    2014  10028
"Other African countries"  2014   8338
"Brazil"                   2014  85288
"Other American countries" 2014   9104
"China"                    2014  21042
"India"                    2014   6372
"Nepal"                    2014   3543
"Other Asian countries"    2014  11535
end

tempfile numerator
save `numerator'

clear
input int D byte region long local_pop
2009 1 3660027
2009 2 2276922
2009 3 2569821
2009 4  737925
2009 5  369714
2009 6  243259
2009 7  258837
2010 1 3650003
2010 2 2271508
2010 3 2594130
2010 4  733699
2010 5  376756
2010 6  243375
2010 7  260576
2011 1 3641191
2011 2 2267769
2011 3 2605266
2011 4  729568
2011 5  382890
2011 6  243535
2011 7  260910
2012 1 3628217
2012 2 2260102
2012 3 2610494
2012 4  725674
2012 5  389095
2012 6  243429
2012 7  259801
2013 1 3607664
2013 2 2244571
2013 3 2612571
2013 4  719157
2013 5  391772
2013 6  242591
2013 7  257398
2014 1 3586851
2014 2 2227815
2014 3 2614567
2014 4  712295
2014 5  392933
2014 6  241024
2014 7  254011
2015 1 3570236
2015 2 2213110
2015 3 2626870
2015 4  705638
2015 5  392712
2015 6  239178
2015 7  250335
2016 1 3556744
2016 2 2197865
2016 3 2635958
2016 4  698893
2016 5  389192
2016 6  237529
2016 7  247368
2017 1 3545137
2017 2 2182753
2017 3 2636948
2017 4  691623
2017 5  385887
2017 6  236093
2017 7  245003
2018 1 3532359
2018 2 2168704
2018 3 2613291
2018 4  684337
2018 5  380445
2018 6  234825
2018 7  243202
2019 1 3518708
2019 2 2150940
2019 3 2573936
2019 4  676251
2019 5  370046
2019 6  233614
2019 7  241976
end

reshape wide local_pop, i(D) j(region)
gen int year = D + 1
drop D

tempfile denom
save `denom'

use `numerator', clear
merge m:1 year using `denom', keep(3) nogen

forval d = 1/7 {
    gen frac_`d' = IMM / local_pop`d'
}

which produces:

. list country year frac_?, noobs sepby(year)

  +--------------------------------------------------------------------------------------------------------------+
  |                  country   year     frac_1     frac_2     frac_3     frac_4     frac_5     frac_6     frac_7 |
  |--------------------------------------------------------------------------------------------------------------|
  |                    Spain   2010   .0024366   .0039167   .0034703   .0120852   .0241213   .0366605   .0344541 |
  |                   France   2010   .0013964   .0022447   .0019889   .0069262   .0138242   .0210105    .019746 |
  |                    Italy   2010   .0013844   .0022254   .0019717   .0068666   .0137052   .0208297    .019576 |
  |           United Kingdom   2010   .0046983   .0075523   .0066915   .0233032   .0465116   .0706901   .0664356 |
  |                  Ukraine   2010   .0135209   .0217342    .019257   .0670624   .1338521   .2034334   .1911898 |
  |                  Romania   2010   .0100628   .0161753   .0143317   .0499102   .0996175   .1514024   .1422903 |
  |                 Moldavia   2010    .004271   .0068654   .0060829   .0211837   .0422813   .0642607   .0603932 |
  | Other European countries   2010   .0105445   .0169496   .0150178   .0522994   .1043861   .1586498   .1491016 |
  |                   Angola   2010   .0063478   .0102037   .0090407   .0314842   .0628405   .0955073   .0897592 |
  |               Cape Verde   2010   .0118879   .0191091   .0169311   .0589626   .1176856   .1788629   .1680981 |
  |            Guinea Bissau   2010   .0052743   .0084781   .0075118   .0261598   .0522133   .0793557   .0745798 |
  |               Mozambique   2010   .0008494   .0013654   .0012098   .0042132   .0084092   .0127806   .0120114 |
  |    Sao Tome and Principe   2010     .00278   .0044688   .0039594   .0137887   .0275213   .0418278   .0393105 |
  |  Other African countries   2010   .0021169   .0034028    .003015   .0104997   .0209567   .0318508   .0299339 |
  |                   Brazil   2010   .0325667   .0523492   .0463826   .1615273   .3223979   .4899921   .4605022 |
  | Other American countries   2010   .0023707   .0038108   .0033765   .0117586   .0234695   .0356698    .033523 |
  |                    China   2010   .0042623   .0068514   .0060705   .0211404   .0421948   .0641292   .0602696 |
  |                    India   2010   .0014243   .0022895   .0020285   .0070644   .0141001   .0214298   .0201401 |
  |                    Nepal   2010   .0002175   .0003496   .0003097   .0010787    .002153   .0032722   .0030753 |
  |    Other Asian countries   2010   .0025552   .0041073   .0036392   .0126734   .0252952   .0384446   .0361308 |
  |--------------------------------------------------------------------------------------------------------------|
  |                    Spain   2011   .0025507   .0040986   .0035889   .0126891    .024711   .0382537   .0357285 |
  |                   France   2011   .0014501   .0023302   .0020404   .0072141   .0140489   .0217483   .0203127 |
  |                    Italy   2011   .0014625     .00235   .0020577   .0072755   .0141683   .0219332   .0204854 |
  |           United Kingdom   2011   .0048425   .0077812   .0068135   .0240903   .0469137   .0726245   .0678305 |
  |                  Ukraine   2011   .0131534   .0211357   .0185072   .0654356   .1274299   .1972676   .1842457 |
  |                  Romania   2011   .0107704   .0173066   .0151542   .0535806   .1043434   .1615285   .1508658 |
  |                 Moldavia   2011   .0037222    .005981   .0052372   .0185171   .0360605   .0558233   .0521383 |
  | Other European countries   2011    .010686    .017171   .0150355   .0531608   .1035259    .160263   .1496838 |
  |                   Angola   2011   .0058436   .0093898    .008222   .0290705   .0566122   .0876384   .0818533 |
  |               Cape Verde   2011   .0119109   .0191393    .016759   .0592545    .115393   .1786338   .1668419 |
  |            Guinea Bissau   2011   .0049674   .0079819   .0069892   .0247118    .048124   .0744982   .0695805 |
  |               Mozambique   2011   .0008205   .0013185   .0011545   .0040821   .0079494   .0123061   .0114938 |
  |    Sao Tome and Principe   2011   .0028148    .004523   .0039605    .014003   .0272696   .0422147    .039428 |
  |  Other African countries   2011    .002134    .003429   .0030025   .0106161   .0206739   .0320041   .0298915 |
  |                   Brazil   2011   .0304918   .0489961   .0429026   .1516903   .2954034   .4572984   .4271115 |
  | Other American countries   2011   .0024321    .003908    .003422    .012099   .0235617   .0364746   .0340668 |
  |                    China   2011   .0045466   .0073057   .0063971   .0226183   .0440471    .068187   .0636858 |
  |                    India   2011   .0014564   .0023403   .0020492   .0072455   .0141099   .0218428    .020401 |
  |                    Nepal   2011   .0003134   .0005036    .000441   .0015592   .0030364   .0047006   .0043903 |
  |    Other Asian countries   2011   .0026425   .0042461    .003718   .0131457   .0256001   .0396302   .0370142 |
  |--------------------------------------------------------------------------------------------------------------|
  |                    Spain   2012    .002531   .0040639   .0035375   .0126321   .0240696   .0378426   .0353225 |
  |                   France   2012   .0014284   .0022934   .0019963   .0071289   .0135835   .0213563   .0199341 |
  |                    Italy   2012   .0014341   .0023027   .0020044   .0071577   .0136384   .0214425   .0200146 |
  |           United Kingdom   2012   .0045724   .0073416   .0063905   .0228204   .0434825   .0683639   .0638113 |
  |                  Ukraine   2012   .0120977   .0194244   .0169081   .0603782   .1150461   .1808775   .1688322 |
  |                  Romania   2012   .0096716   .0155289   .0135172   .0482697   .0919742   .1446034   .1349737 |
  |                 Moldavia   2012   .0031591   .0050724   .0044153   .0157669   .0300426   .0472335    .044088 |
  | Other European countries   2012   .0101678   .0163257   .0142108   .0507465   .0966936   .1520233   .1418995 |
  |                   Angola   2012   .0054578   .0087632    .007628   .0272394   .0519026   .0816022    .076168 |
  |               Cape Verde   2012   .0116412   .0186915   .0162701   .0581001   .1107054    .174053   .1624621 |
  |            Guinea Bissau   2012   .0047957   .0077001   .0067026   .0239347   .0456058   .0717022   .0669273 |
  |               Mozambique   2012   .0007967   .0012792   .0011135   .0039763   .0075766    .011912   .0111188 |
  |    Sao Tome and Principe   2012   .0027941   .0044863   .0039052   .0139452   .0265716   .0417763   .0389943 |
  |  Other African countries   2012   .0022185   .0035621   .0031006   .0110723   .0210974   .0331698   .0309609 |
  |                   Brazil   2012    .028979   .0465294   .0405018   .1446308   .2755831   .4332765    .404423 |
  | Other American countries   2012   .0024778   .0039784    .003463   .0123662   .0235629    .037046    .034579 |
  |                    China   2012   .0047199   .0075784   .0065966   .0235564    .044885   .0705689   .0658695 |
  |                    India   2012   .0015308   .0024579   .0021395   .0076401   .0145577   .0228879   .0213637 |
  |                    Nepal   2012   .0004674   .0007505   .0006533   .0023329   .0044451   .0069887   .0065233 |
  |    Other Asian countries   2012   .0028013   .0044978   .0039151   .0139809   .0266395   .0418831   .0390939 |
  |--------------------------------------------------------------------------------------------------------------|
  |                    Spain   2013   .0026297   .0042215   .0036549   .0131478    .024521   .0391942   .0367243 |
  |                   France   2013    .001452   .0023309    .002018   .0072595   .0135391   .0216408   .0202771 |
  |                    Italy   2013   .0014114   .0022658   .0019617   .0070569   .0131613   .0210369   .0197112 |
  |           United Kingdom   2013   .0045397   .0072877   .0063095   .0226975   .0423316   .0676624   .0633985 |
  |                  Ukraine   2013   .0113207   .0181735   .0157342   .0566012   .1055629   .1687309   .1580979 |
  |                  Romania   2013   .0094272   .0151338   .0131025   .0471341   .0879066   .1405091   .1316546 |
  |                 Moldavia   2013   .0027474   .0044104   .0038184   .0137362   .0256184   .0409483   .0383678 |
  | Other European countries   2013   .0102929   .0165236   .0143057   .0514625   .0959791   .1534123   .1437446 |
  |                   Angola   2013   .0055033   .0088346   .0076487   .0275151   .0513165   .0820239    .076855 |
  |               Cape Verde   2013    .011579   .0185881   .0160931   .0578924   .1079711   .1725801   .1617045 |
  |            Guinea Bissau   2013   .0048437   .0077758   .0067321   .0242175   .0451663   .0721935   .0676441 |
  |               Mozambique   2013   .0007786   .0012499   .0010822   .0038929   .0072604    .011605   .0108737 |
  |    Sao Tome and Principe   2013   .0028028   .0044994   .0038954   .0140132    .026135    .041774   .0391415 |
  |  Other African countries   2013   .0022873    .003672   .0031791   .0114363    .021329   .0340921   .0319437 |
  |                   Brazil   2013   .0251468    .040369   .0349505   .1257286   .2344877   .3748033   .3511842 |
  | Other American countries   2013   .0024965   .0040078   .0034698   .0124822   .0232797     .03721   .0348651 |
  |                    China   2013   .0050838   .0081611   .0070657   .0254177   .0474049   .0757716   .0709966 |
  |                    India   2013    .001649   .0026472   .0022919   .0082447   .0153767    .024578   .0230292 |
  |                    Nepal   2013   .0007031   .0011287   .0009772   .0035154   .0065562   .0104794   .0098191 |
  |    Other Asian countries   2013   .0029838     .00479   .0041471   .0149185   .0278235   .0444729   .0416704 |
  |--------------------------------------------------------------------------------------------------------------|
  |                    Spain   2014   .0026865    .004318   .0037098   .0134769   .0247389    .039952   .0376538 |
  |                   France   2014   .0018131   .0029141   .0025037   .0090954   .0166959   .0269631    .025412 |
  |                    Italy   2014   .0014769   .0023737   .0020394   .0074087   .0135997   .0219629   .0206995 |
  |           United Kingdom   2014     .00459   .0073774   .0063382   .0230256   .0422669   .0682589   .0643323 |
  |                  Ukraine   2014   .0104802   .0168446    .014472   .0525741   .0965077   .1558549   .1468893 |
  |                  Romania   2014   .0087328   .0140361    .012059   .0438082   .0804167   .1298688    .122398 |
  |                 Moldavia   2014   .0023445   .0037682   .0032374    .011761   .0215891   .0348653   .0328596 |
  | Other European countries   2014   .0105453   .0169493   .0145619   .0529008   .0971075   .1568236   .1478022 |
  |                   Angola   2014   .0053991   .0086778   .0074555   .0270845   .0497177   .0802915   .0756727 |
  |               Cape Verde   2014   .0112436   .0180716   .0155261   .0564035   .1035373   .1672074   .1575886 |
  |            Guinea Bissau   2014    .004914   .0078982   .0067857   .0246511   .0452508   .0730777   .0688739 |
  |               Mozambique   2014   .0007797   .0012532   .0010767   .0039115   .0071802   .0115956   .0109286 |
  |    Sao Tome and Principe   2014   .0027796   .0044677   .0038384   .0139441   .0255965   .0413371   .0389591 |
  |  Other African countries   2014   .0023112   .0037147   .0031915   .0115941   .0212828   .0343706   .0323934 |
  |                   Brazil   2014   .0236408   .0379975   .0326452   .1185944   .2176981   .3515712   .3313468 |
  | Other American countries   2014   .0025235    .004056   .0034847   .0126593    .023238   .0375282   .0353694 |
  |                    China   2014   .0058326   .0093746   .0080541   .0292593   .0537098   .0867386   .0817489 |
  |                    India   2014   .0017662   .0028388    .002439   .0088604   .0162646   .0262664   .0247554 |
  |                    Nepal   2014   .0009821   .0015785   .0013561   .0049266   .0090435   .0146048   .0137647 |
  |    Other Asian countries   2014   .0031974   .0051391   .0044152   .0160396   .0294431   .0475492   .0448139 |
  +--------------------------------------------------------------------------------------------------------------+

[Paris Rira]

Thank you so much Prof Clyde and Ken for getting back to me.
Actually, I don't understand completely your point. Mainly because I am thinking about the fraction. If it is not possible to merge these two datasets, the fraction must be wrong. I mean how the author did it if is not possible?

[Hemanshu Kumar]

Paris Rira in case you missed #4 because we posted around the same time, see if that solves your problem.

[Clyde Schechter]

Actually, I don't understand completely your point. Mainly because I am thinking about the fraction. If it is not possible to merge these two datasets, the fraction must be wrong.

What is not possible, without more information, is pairing up the numerators with the denominators. Think about the first observation in the first data set : "Spain" 2010 8918. We know that 8,918 will be the numerator of the fraction for this observation. But what will the denominator be? Presumably it will be one of the observations from the second data set with year = 2010. But there are seven of those:

2010 1 3650003
2010 2 2271508
2010 3 2594130
2010 4 733699
2010 5 376756
2010 6 243375
2010 7 260576

Which of these regions does Spain belong to? That is the missing information that is needed to combine the two data sets.

Now, Hemanshu Kumar has offered you a solution in which 7 fractions are calculated, one for each domain. Perhaps this is what you have in mind. But if each country is supposed to be associated with a single domain, you cannot proceed until you have data showing which countries are in which domains.

[Paris Rira]

Thank you so much Hemanshu.
Actually, the whole fraction is :


I already computed the left-side fraction in the above picture. Three variables: country IMshare region. IMshare is the variable that should be multiplied by the second fraction on the right side which Hemanshu just proposed its solution.

* Example generated by -dataex-. For more info, type help dataex
clear
input str22 country float(IMshare region)
"Angola"                  .012010016 6
"Angola"                    .3076097 3
"Angola"                   .02596208 7
"Angola"                   .04139623 4
"Angola"                   .04599581 5
"Angola"                    .2612562 2
"Angola"                    .3057699 1
"Brazil"                  .017064847 6
"Brazil"                   .23559526 3
"Brazil"                  .019875526 7
"Brazil"                  .011242722 4
"Brazil"                   .00963662 5
"Brazil"                   .26721543 2
"Brazil"                    .4393696 1
"CapeVerde"              .0043649296 6
"CapeVerde"                 .9084982 3
"CapeVerde"              .0009699844 7
"CapeVerde"                .01465754 4
"CapeVerde"               .031793933 5
"CapeVerde"               .024411274 2
"CapeVerde"               .015304198 1
"China"                   .012987013 6
"China"                     .7467533 3
"China"                   .032467533 7
"China"                   .025974026 4
"China"                   .025974026 5
"China"                   .064935066 2
"China"                     .0909091 1
"France"                 .0038910506 6
"France"                    .1538207 3
"France"                  .016806027 7
"France"                  .034936666 4
"France"                  .037171952 5
"France"                    .3157546 2
"France"                     .437619 1
"GuineaBissau"           .0008880995 6
"GuineaBissau"               .722913 3
"GuineaBissau"            .005328597 7
"GuineaBissau"            .027531084 4
"GuineaBissau"            .007992895 5
"GuineaBissau"             .09236234 2
"GuineaBissau"             .14298402 1
"India"                   .004524887 6
"India"                     .7647059 3
"India"                   .009049774 7
"India"                   .036199097 4
"India"                    .01357466 5
"India"                    .09049774 2
"India"                    .08144797 1
"Italy"                            0 6
"Italy"                    .58676654 3
"Italy"                    .02372035 7
"Italy"                    .13233458 4
"Italy"                    .02496879 5
"Italy"                    .06866417 2
"Italy"                    .16354556 1
"Mozambique"               .01220339 6
"Mozambique"                .3703955 3
"Mozambique"               .05062147 7
"Mozambique"               .04610169 4
"Mozambique"              .018305086 5
"Mozambique"                .2302825 2
"Mozambique"                .2720904 1
"OtherAfricancountries"   .009181331 6
"OtherAfricancountries"    .28194338 3
"OtherAfricancountries"     .1530222 7
"OtherAfricancountries"   .024866106 4
"OtherAfricancountries"    .03749043 5
"OtherAfricancountries"    .21537873 2
"OtherAfricancountries"    .27811784 1
"OtherAmericancountries"    .0433145 6
"OtherAmericancountries"   .33427495 3
"OtherAmericancountries"   .06779661 7
"OtherAmericancountries"   .05461394 4
"OtherAmericancountries"   .05461394 5
"OtherAmericancountries"    .1967985 2
"OtherAmericancountries"   .24858756 1
"OtherAsiancountries"    .0014814815 6
"OtherAsiancountries"       .4592593 3
"OtherAsiancountries"     .014074074 7
"OtherAsiancountries"     .036296297 4
"OtherAsiancountries"     .018518519 5
"OtherAsiancountries"            .18 2
"OtherAsiancountries"      .29037037 1
"OtherEuropeancountries"  .004007757 6
"OtherEuropeancountries"    .3794441 3
"OtherEuropeancountries"  .017194571 7
"OtherEuropeancountries"   .08752424 4
"OtherEuropeancountries"   .05778927 5
"OtherEuropeancountries"   .15979315 2
"OtherEuropeancountries"   .29424694 1
"SaoTomeandPrincipe"     .0019392372 6
"SaoTomeandPrincipe"        .8377505 3
"SaoTomeandPrincipe"      .001292825 7
"SaoTomeandPrincipe"      .022624435 4
"SaoTomeandPrincipe"      .022624435 5
"SaoTomeandPrincipe"       .07110537 2
"SaoTomeandPrincipe"       .04266322 1
"Spain"                  .0043311473 6
"Spain"                     .6089593 3
end

According to #4 how can I multiply these 2 fractions with each other?
Really appreciated.

[Hemanshu Kumar]

Consider this:

clear
input str24 country int year long IMM
"Spain"                    2010   8918
"France"                   2010   5111
"Italy"                    2010   5067
"United Kingdom"           2010  17196
"Ukraine"                  2010  49487
"Romania"                  2010  36830
"Moldavia"                 2010  15632
"Other European countries" 2010  38593
"Angola"                   2010  23233
"Cape Verde"               2010  43510
"Guinea Bissau"            2010  19304
"Mozambique"               2010   3109
"Sao Tome and Principe"    2010  10175
"Other African countries"  2010   7748
"Brazil"                   2010 119195
"Other American countries" 2010   8677
"China"                    2010  15600
"India"                    2010   5213
"Nepal"                    2010    796
"Other Asian countries"    2010   9352
"Spain"                    2011   9310
"France"                   2011   5293
"Italy"                    2011   5338
"United Kingdom"           2011  17675
"Ukraine"                  2011  48010
"Romania"                  2011  39312
"Moldavia"                 2011  13586
"Other European countries" 2011  39004
"Angola"                   2011  21329
"Cape Verde"               2011  43475
"Guinea Bissau"            2011  18131
"Mozambique"               2011   2995
"Sao Tome and Principe"    2011  10274
"Other African countries"  2011   7789
"Brazil"                   2011 111295
"Other American countries" 2011   8877
"China"                    2011  16595
"India"                    2011   5316
"Nepal"                    2011   1144
"Other Asian countries"    2011   9645
"Spain"                    2012   9216
"France"                   2012   5201
"Italy"                    2012   5222
"United Kingdom"           2012  16649
"Ukraine"                  2012  44050
"Romania"                  2012  35216
"Moldavia"                 2012  11503
"Other European countries" 2012  37023
"Angola"                   2012  19873
"Cape Verde"               2012  42388
"Guinea Bissau"            2012  17462
"Mozambique"               2012   2901
"Sao Tome and Principe"    2012  10174
"Other African countries"  2012   8078
"Brazil"                   2012 105518
"Other American countries" 2012   9022
"China"                    2012  17186
"India"                    2012   5574
"Nepal"                    2012   1702
"Other Asian countries"    2012  10200
"Spain"                    2013   9541
"France"                   2013   5268
"Italy"                    2013   5121
"United Kingdom"           2013  16471
"Ukraine"                  2013  41074
"Romania"                  2013  34204
"Moldavia"                 2013   9968
"Other European countries" 2013  37345
"Angola"                   2013  19967
"Cape Verde"               2013  42011
"Guinea Bissau"            2013  17574
"Mozambique"               2013   2825
"Sao Tome and Principe"    2013  10169
"Other African countries"  2013   8299
"Brazil"                   2013  91238
"Other American countries" 2013   9058
"China"                    2013  18445
"India"                    2013   5983
"Nepal"                    2013   2551
"Other Asian countries"    2013  10826
"Spain"                    2014   9692
"France"                   2014   6541
"Italy"                    2014   5328
"United Kingdom"           2014  16559
"Ukraine"                  2014  37809
"Romania"                  2014  31505
"Moldavia"                 2014   8458
"Other European countries" 2014  38044
"Angola"                   2014  19478
"Cape Verde"               2014  40563
"Guinea Bissau"            2014  17728
"Mozambique"               2014   2813
"Sao Tome and Principe"    2014  10028
"Other African countries"  2014   8338
"Brazil"                   2014  85288
"Other American countries" 2014   9104
"China"                    2014  21042
"India"                    2014   6372
"Nepal"                    2014   3543
"Other Asian countries"    2014  11535
end

tempfile numerator
save `numerator'

clear
input int D byte region long local_pop
2009 1 3660027
2009 2 2276922
2009 3 2569821
2009 4  737925
2009 5  369714
2009 6  243259
2009 7  258837
2010 1 3650003
2010 2 2271508
2010 3 2594130
2010 4  733699
2010 5  376756
2010 6  243375
2010 7  260576
2011 1 3641191
2011 2 2267769
2011 3 2605266
2011 4  729568
2011 5  382890
2011 6  243535
2011 7  260910
2012 1 3628217
2012 2 2260102
2012 3 2610494
2012 4  725674
2012 5  389095
2012 6  243429
2012 7  259801
2013 1 3607664
2013 2 2244571
2013 3 2612571
2013 4  719157
2013 5  391772
2013 6  242591
2013 7  257398
2014 1 3586851
2014 2 2227815
2014 3 2614567
2014 4  712295
2014 5  392933
2014 6  241024
2014 7  254011
2015 1 3570236
2015 2 2213110
2015 3 2626870
2015 4  705638
2015 5  392712
2015 6  239178
2015 7  250335
2016 1 3556744
2016 2 2197865
2016 3 2635958
2016 4  698893
2016 5  389192
2016 6  237529
2016 7  247368
2017 1 3545137
2017 2 2182753
2017 3 2636948
2017 4  691623
2017 5  385887
2017 6  236093
2017 7  245003
2018 1 3532359
2018 2 2168704
2018 3 2613291
2018 4  684337
2018 5  380445
2018 6  234825
2018 7  243202
2019 1 3518708
2019 2 2150940
2019 3 2573936
2019 4  676251
2019 5  370046
2019 6  233614
2019 7  241976
end

reshape wide local_pop, i(D) j(region)
gen int year = D + 1
drop D

tempfile denom
save `denom'

use `numerator', clear
merge m:1 year using `denom', keep(3) nogen

forval d = 1/7 {
    gen frac_`d' = IMM / local_pop`d'
}

tempfile second_frac
save `second_frac'

clear
input str22 country float(IMshare region)
"Angola"                  .012010016 6
"Angola"                    .3076097 3
"Angola"                   .02596208 7
"Angola"                   .04139623 4
"Angola"                   .04599581 5
"Angola"                    .2612562 2
"Angola"                    .3057699 1
"Brazil"                  .017064847 6
"Brazil"                   .23559526 3
"Brazil"                  .019875526 7
"Brazil"                  .011242722 4
"Brazil"                   .00963662 5
"Brazil"                   .26721543 2
"Brazil"                    .4393696 1
"CapeVerde"              .0043649296 6
"CapeVerde"                 .9084982 3
"CapeVerde"              .0009699844 7
"CapeVerde"                .01465754 4
"CapeVerde"               .031793933 5
"CapeVerde"               .024411274 2
"CapeVerde"               .015304198 1
"China"                   .012987013 6
"China"                     .7467533 3
"China"                   .032467533 7
"China"                   .025974026 4
"China"                   .025974026 5
"China"                   .064935066 2
"China"                     .0909091 1
"France"                 .0038910506 6
"France"                    .1538207 3
"France"                  .016806027 7
"France"                  .034936666 4
"France"                  .037171952 5
"France"                    .3157546 2
"France"                     .437619 1
"GuineaBissau"           .0008880995 6
"GuineaBissau"               .722913 3
"GuineaBissau"            .005328597 7
"GuineaBissau"            .027531084 4
"GuineaBissau"            .007992895 5
"GuineaBissau"             .09236234 2
"GuineaBissau"             .14298402 1
"India"                   .004524887 6
"India"                     .7647059 3
"India"                   .009049774 7
"India"                   .036199097 4
"India"                    .01357466 5
"India"                    .09049774 2
"India"                    .08144797 1
"Italy"                            0 6
"Italy"                    .58676654 3
"Italy"                    .02372035 7
"Italy"                    .13233458 4
"Italy"                    .02496879 5
"Italy"                    .06866417 2
"Italy"                    .16354556 1
"Mozambique"               .01220339 6
"Mozambique"                .3703955 3
"Mozambique"               .05062147 7
"Mozambique"               .04610169 4
"Mozambique"              .018305086 5
"Mozambique"                .2302825 2
"Mozambique"                .2720904 1
"OtherAfricancountries"   .009181331 6
"OtherAfricancountries"    .28194338 3
"OtherAfricancountries"     .1530222 7
"OtherAfricancountries"   .024866106 4
"OtherAfricancountries"    .03749043 5
"OtherAfricancountries"    .21537873 2
"OtherAfricancountries"    .27811784 1
"OtherAmericancountries"    .0433145 6
"OtherAmericancountries"   .33427495 3
"OtherAmericancountries"   .06779661 7
"OtherAmericancountries"   .05461394 4
"OtherAmericancountries"   .05461394 5
"OtherAmericancountries"    .1967985 2
"OtherAmericancountries"   .24858756 1
"OtherAsiancountries"    .0014814815 6
"OtherAsiancountries"       .4592593 3
"OtherAsiancountries"     .014074074 7
"OtherAsiancountries"     .036296297 4
"OtherAsiancountries"     .018518519 5
"OtherAsiancountries"            .18 2
"OtherAsiancountries"      .29037037 1
"OtherEuropeancountries"  .004007757 6
"OtherEuropeancountries"    .3794441 3
"OtherEuropeancountries"  .017194571 7
"OtherEuropeancountries"   .08752424 4
"OtherEuropeancountries"   .05778927 5
"OtherEuropeancountries"   .15979315 2
"OtherEuropeancountries"   .29424694 1
"SaoTomeandPrincipe"     .0019392372 6
"SaoTomeandPrincipe"        .8377505 3
"SaoTomeandPrincipe"      .001292825 7
"SaoTomeandPrincipe"      .022624435 4
"SaoTomeandPrincipe"      .022624435 5
"SaoTomeandPrincipe"       .07110537 2
"SaoTomeandPrincipe"       .04266322 1
"Spain"                  .0043311473 6
"Spain"                     .6089593 3
end

reshape wide IMshare, i(country) j(region)
merge 1:m country using `second_frac', keep(3) keepusing(year frac_?) nogen
sort country year
gen IM_hat = 0

forval d = 1/7 {
    replace IM_hat = IM_hat + IMshare`d' * frac_`d'
}

which produces:

. list country year IM_hat in 1/15, noobs sepby(country)

  +---------------------------+
  | country   year     IM_hat |
  |---------------------------|
  |  Angola   2010   .0150588 |
  |  Angola   2011   .0137541 |
  |  Angola   2012   .0127772 |
  |  Angola   2013   .0128234 |
  |  Angola   2014   .0125483 |
  |---------------------------|
  |  Brazil   2010    .061662 |
  |  Brazil   2011   .0574422 |
  |  Brazil   2012   .0544215 |
  |  Brazil   2013   .0471192 |
  |  Brazil   2014    .044248 |
  |---------------------------|
  |   China   2010   .0098002 |
  |   China   2011   .0103496 |
  |   China   2012     .01068 |
  |   China   2013   .0114491 |
  |   China   2014   .0130891 |
  +---------------------------+

Note that before doing this, you should make sure the country strings are the same across datasets. Currently, they are not -- the strings in the dataset you posted in #8 lack spaces, for example.

[Paris Rira]

Dear Hemanshu,

It worked perfectly. You saved my day. Thank you so much.
First, I adjusted country.

replace country = "CapeVerde" if country == "Cape Verde"
replace country = "GuineaBissau" if country == "Guinea Bissau"
replace country = "OtherAfricancountries" if country == "Other African countries"
replace country = "OtherAmericancountries" if country == "Other American countries"
replace country = "OtherAsiancountries" if country == "Other Asian countries"
replace country = "SaoTomeandPrincipe" if country == "Sao Tome and Principe" 
replace country = "UnitedKingdom" if country == "United Kingdom
replace country = "OtherEuropeancountries" if country == "Other European countries"

. list country year IM_hat in 1/15, noobs sepby(country)

  +---------------------------+
  | country   year     IM_hat |
  |---------------------------|
  |  Angola   2010   .0149146 |
  |  Angola   2011   .0136274 |
  |  Angola   2012   .0126637 |
  |  Angola   2013    .012714 |
  |  Angola   2014   .0124442 |
  |  Angola   2015   .0116149 |
  |  Angola   2016   .0108903 |
  |  Angola   2017   .0108836 |
  |  Angola   2018   .0119603 |
  |  Angola   2019   .0148804 |
  |---------------------------|
  |  Brazil   2010   .0619204 |
  |  Brazil   2011    .057673 |
  |  Brazil   2012   .0546319 |
  |  Brazil   2013   .0472939 |
  |  Brazil   2014   .0444072 |
  +---------------------------+