How to merge two datasets so that the values get multiplied by each other in each cell.

[Aishwary Trivedi]

Suppose, I have two datasets given below, now how can I merge the datasets so that resulting values in the merged dataset gets multiplied with each other. How should i go about it? For example: Australia(1996) = [1st dataset] * Australia (1996 HS-01) = [2nd dataset] and so on.

* Example generated by -dataex-. For more info, type help dataex
clear
year (AfghanistanIslamicRepof Albania Algeria Angola Anguilla AntiguaandBarbuda ArmeniaRepof ArubaKingdomoftheNetherland Australia Austria)
1989         .         . 1.7805092             .         .         .          . 1.4666357  1.238932  14.81106
1990         .         . 2.3200164  1.108489e-17 2.3214445         .          . 1.4727408 1.2781003 12.469996
1991         .         .  5.778347  3.599408e-17  2.329948         .          . 1.4913995 1.2677894  12.69543
1992         . 21.050524  8.729126  6.368768e-16 2.3147259         .          .  1.503625  1.318393 12.063948
1993         .   51.4552 10.926892   9.67757e-14 2.3442645         . .063500166 1.5366895 1.4072763  12.85402
1994         .   56.9835  20.63763 2.2151773e-11 2.3648777         .  68.153564 1.5921566 1.3007553 12.664124
1995         .  58.53213 35.418964  2.754596e-08 2.3322291         .  257.25287  1.600767 1.3056957   11.1169
1996         .  72.26252  46.90939  .00005288584 2.3469908         .   302.5596 1.6053406  1.233018 11.552455
1997         . 134.02824  51.08487   .0002950785 2.2958624         .   399.4227 1.6157292  1.273254 13.183474
1998         .  161.0418  53.73792   .0010330025 2.3203835  2.873869   439.6783  1.620776 1.4940015  13.28939
1999         . 144.61383  61.17844     .02500585 2.3071392  2.843869   458.9094  1.622241 1.4446623         .
2000         . 146.07794  67.12816      .3698684  2.337161  2.772204   444.0823 1.6327102  1.624464         .
2001         . 146.24883 69.809715     1.9957954  2.378907   2.73383   458.3041 1.6336223 1.8489385         .
2002         . 151.55124  71.92107      8.099205 2.3543591  2.755941    470.942   1.66143 1.7842977         .
2003         . 129.47128  71.22222      26.90513 2.3759816  2.748488   486.7805 1.6839507 1.5015388         .
2004  35.08702 108.77425  67.14297      42.11786 2.4148765  2.731165   467.3898 1.6815217   1.31984         .
2005  39.55905 104.64574  66.94816      52.25508  2.443242  2.696983   390.3276 1.6816036   1.26242         .
2006  41.27896 101.94274 65.784874      52.88837  2.566644 2.6594086   353.6637  1.687828   1.28434         .
2007  43.64998  94.03997  63.25159      55.09134  2.605848 2.6222625   295.1869  1.729508 1.1499054         .
2008  53.44746   86.8458  59.53136      58.37217 2.6964934 2.6600084  277.02225  1.814733 1.1527679         .
2009  50.05978 100.87175  71.05968     70.437164  2.687733  2.654813   341.3327 1.7823178 1.2662634         .
2010  46.45246 103.93667  74.38599      91.90572       2.7       2.7   373.6605      1.79 1.0901595         .
2011  50.66568 101.16215  73.90469     103.33727 2.7410235   2.70785   388.7258  1.810119  .9708448         .
2012  57.55392  108.4296  83.81477     113.47013  2.723274 2.7425385   421.2708 1.7845488  .9642726         .
2013  66.24287  106.4024  87.30865     122.98674 2.6878986 2.7315824   447.8219  1.717066 1.0442442         .
2014 70.536255 106.21573   89.7697     132.23444  2.638283  2.717261    460.784  1.696777 1.1278877         .
2015   74.7494 131.12564 117.40384      176.0725  2.609523   2.74034   548.5775 1.7028126 1.3721024         .
2016  85.52621 127.15306 134.08046      309.7698  2.563564  2.692954   537.0112  1.665939 1.3868723         .
2017   88.1177 121.90525  140.5621     399.26465  2.542581  2.700927    533.371 1.6144195 1.3427736         .
2018  91.71642 110.08546 150.31595      710.5589  2.491299  2.668355  534.07556 1.6330698  1.370266         .
2019  99.38748 111.54207 154.08485     1178.9629 2.4669015  2.658373   529.3395 1.6722918  1.469829         .
2020         . 110.74482 165.57846             .         .         .  538.65686         . 1.4790536         .
end

* Example generated by -dataex-. For more info, type help dataex
clear
iYear str210 ProductSector float(Albania AntiguaandBarbuda Argentina Armenia Australia)
1996 "HS - 01"                  . .    .005412923 .   .009761705
1996 "HS - 02 "                 . .    .000595907 . .00012810821
1996 "HS - 03                   . .   .0000435198 .   .002957913
1996 "HS - 04"                  . .    .007955423 .   .007539196
1996 "HS - 05"                  . .    .015250857 .    .03253398
1997 "HS - 01"                  . .    .003493172 .   .004160443
1997 "HS - 02"                  . .   .0012534298 .    .01326288
1997 "HS - 03"                  . .    .001726718 .   .010663555
1997 "HS - 04"                  . .  .00031747445 .   .017899036
1997 "HS - 05"                  . .    .001720109 . .00010939194
end

[William Lisowski]

How does this question differ from the one asked and answered at

https://www.statalist.org/forums/for...g-two-datasets

In particular I agree with the recommendations of both respondents there, which is that you should be storing your data in a long layout rather than a wide layout.

[Aishwary Trivedi]

I agree with you William Lisowski that it is somewhat similar to my previous post where Clyde Schechter suggested me some useful codes. But while I am trying to merge both the datasets in this case, resulting values remains unchanged. Let me once again show the dataset in a comprehensible manner. There are some countries missing in datatset1 and some other countries missing in datatset2. Moreover, this extra variable PS(ProductSector) which contains 97 sectors for each year makes it more complex. Please suggest how to merge both the datasets effectively.
DATASET-1

Year PS    Argentina       Australia     Brazil    Canada    India)
1996  1    .005412923   .009761705   .021289896   .16551557             .
1996  2    .000595907 .00012810821   .001922998   .07750466  5.583215e-06
1996  3   .0000435198   .002957913  .0003875762   .11520422   .0000263602
1996  4    .007955423   .007539196     .0306171   .13337472  .00006167813
1996  5    .015250857    .03253398   .017567292   .07675288    .002637858
1996  6    .003493172   .004160443   .002276731    .3606265    .002714305
1996  7   .0012534298    .01326288   .006882724    .3898989   .0010493217
1996  8    .001726718   .010663555   .015049414   .17263103    .011813587
1996  9  .00031747445   .017899036    .00567275    .6247506   .0005122751
1996 10    .001720109 .00010939194    .01637734   .01904136 2.5047459e-07
1996 11   .0019767391   .006426158   .018081691   .15395814  .00002265323
1996 12    .005288029   .009656722   .008722636   .02489923  .00021144748
1996 13     .02211273    .03087043    .04993448   .06307018    .017548036
1996 14   .0023164314  .0028714926   .029666135   .04277401    .003228264
1996 15    .002928364  .0037859546   .004927469   .11647154    .018047603
1996 16      .0010688    .02471223  .0022396517   .26705992 .000018143983
1996 17   .0042029526    .02684881    .01169383   .28744093    .001654093
1996 18    .006509231   .016741086    .01101217    .4494169  .00002282757
1996 19    .007220794   .010665032   .014054524    .5118221    .021851575
1996 20    .006030358   .009497675   .010786434    .2486253  8.543576e-06
1996 21     .01541473    .02148049    .02358405   .23319013     .02686504
1996 22    .003537237     .0240996     .0771867   .14182587   .0003387502
1996 23   .0043448806   .004895512    .00292151   .13918817   .0004737046
1996 24   .0001914133   .004732565   .004171709 .0041859327  .00019484505
1996 25    .009544675   .013911826    .01171264   .19207713    .009171514
1996 26 .000035927405  .0022411484   .001800989     .340304    .007865751
1996 27    .006805457    .01407332    .05945707   .15685534    .005803204
1996 28    .008936951    .02996735   .033603705   .16530456    .010543867
1996 29     .02182444    .02336423   .068946555   .10555278    .020688225
1996 30    .010402986    .03109907   .021174217   .16109854   .0011136503
1996 31     .05619381    .09411073    .04671761   .07256504    .032817595
1996 32    .015798293    .02190727   .036375914   .25390556    .004155384
1996 33      .0121169    .03077062    .01512533       .2295    .000420718
1996 34    .024961686    .02957504    .02725441   .28369874     .00557252
1996 35    .010916688   .026904374   .031362236   .18759064     .00275323
1996 36    .008250637    .06514726  .0027768505    .3643231   .0018722123
1996 37    .009746832    .06935137    .04313864   .13898335    .006733587
1996 38     .01953846    .02547904    .02789035   .17492287    .006317493
1996 39     .01486332   .020432265   .032280784   .21445473    .004851667
1996 40    .010485123    .02513504    .03872368    .3231668    .004159508
1996 41    .000741592  .0005948011  .0046388335   .04236137    .006883563
1996 42    .003616494   .017351704   .008854772   .09712918   .0004639031
1996 43  .00028222022 .00017748294  .0001082553    .1929896   .0004114978
1996 44   .0006247138   .008362416  .0003743146   .15039323   .0004774384
1996 45    .005849908    .04461202   .009488012   .46924525    .009415307
1996 46    .005950562   .017074218    .13993771   .30654415  .00022869285
1996 47    .010362303  .0038930916    .02044946   .06017964     .02042743
1996 48    .008346957   .023530606   .028377613    .2439417   .0041921143
1996 49    .004334345    .05278573    .01874489    .3842102    .004505226
1996 50    .003346217    .17712426   .029707076   .05699746    .008109384
1996 51    .001375542  .0011008654   .003505378    .2128257    .069497414
1996 52   .0018810314   .005920296    .02729212   .09789398   .0011306499
1996 53     .01520171    .02767783   .011378683   .16282643    .016648134
1996 54   .0081143575   .013419732    .03958957   .20863837    .002983462
1996 55    .010861279    .01916884   .036523197    .2079764     .01144998
1996 56    .009178382    .02927494    .02348635   .27284735     .00148111
1996 56    .009178382    .02927494    .02348635   .27284735     .00148111
1996 57    .011667924   .008853852   .013208172    .3939994   .0011297581
1996 58   .0022094462    .01418644   .017264405   .11621238   .0019864219
1996 59    .005949356   .031456996    .01271354   .30637875    .004374433
1996 60   .0042629507   .031964306   .010241723    .3864517   .0005371182
1996 61   .0021944598    .00795275   .006357198   .11126396  .00004792333
1996 62   .0017026288   .005853608   .008596722   .07815123  .00003734342
1996 63    .006739212   .021930637   .012327651   .26582518    .011437964
1996 64   .0022092115   .014485977   .012609435   .09123532    .003489175
1996 65   .0040906947    .03426809   .014045555   .15078725 .000026429125
1996 66      .0116533   .017159987   .033730853   .25533256    .005257613
1996 67    .012765294    .03968953    .10190888   .10167006   .0011619121
1996 68    .007788125     .0301459   .020502046   .26162264    .003366434
1996 69    .007807021   .019924775   .025516523   .22380675    .004117709
1996 70    .005009144   .015347328   .015893767    .3357402    .003225538
1996 71   .0001720959  .0032916865   .000853719    .1035111    .021531414
1996 72    .002340086   .006054145   .006893443   .28956455     .02979339
1996 73     .00961367    .02074197   .016504394     .269932    .005004544
1996 74   .0012119224   .005603096    .00813138   .23736276     .02952065
1996 75    .002558758    .02284907   .015675021    .2012739    .007635446
1996 76    .014487525   .007768621   .037160512    .2972752    .002575617
1996 77   .0017969486   .001542431   .005292908    .3167117    .019546947
1996 78   .0038820275   .005485375   .005309915    .3172928     .04272653
1996 79    .003086012  .0023170013     .0055437    .3244107   .0040726964
1996 80    .003747034   .021897884    .02311055   .13220201    .010059632
1996 81    .014616858    .04435155     .0392373   .25329092    .003792971
1996 82   .0045793853   .018496266   .016613312    .4348763   .0011816168
1996 83     .01063628   .035657413   .026205974   .20101996     .00606537
1996 84    .007793189   .018534303    .02210317   .13647923    .002996999
1996 85   .0030212793   .034630064   .010585407    .6801313     .01029792
1996 86   .0038835446   .024646277   .008183343   .58850527   .0005256764
1996 87    .007030264    .05594212   .017181918   .07557124    .018360004
1996 88    .017291458    .12408047   .008539211   .12826459     .06224852
1996 89    .007503517   .033528782    .02468535   .12356672    .004937335
1996 90   .0033941504     .0293272   .007566995    .1337502    .002706017
1996 91    .005415997    .03545486    .03559451   .08404399  .00026050673
1996 92    .005103546    .07348242  .0019221066    .2865976   .0001513357
1996 93    .014554962   .012512697   .013311894    .4298844   .0007868752
1996 94     .00369776   .031297006   .010347588   .17580058   .0009833977
1996 95    .014225993   .037229884   .020797146   .18246177   .0014945463
1996 96  .00008626976    .00721618 .00033961135  .027917065  .00025453427
1997  1    .005766634    .01490115    .02315075    .1534583             .
1997  2   .0007933686 .00020810924   .003241023   .08485008             .
1997  3 .000068952075  .0014792576  .0004966396   .13099173 .000028935674
end

DATASET -2

Year (Australia Brazil         Canada    India)
1989  1.238932  6.318592e-13  1.337232  6.010523
1990 1.2781003 4.4035364e-10  1.310087  6.703764
1991 1.2677894 1.3399688e-08   1.30362  9.515412
1992  1.318393 1.5185393e-06 1.3547595 11.766012
1993 1.4072763   .0005860761  1.430694 14.296855
1994 1.3007553     .21582076 1.4784745 15.804987
1995 1.3056957      .4811425 1.4763154  17.51456
1996  1.233018     .59265333 1.4473275 20.262354
1997  1.273254      .6641367 1.4594097 21.745016
1998 1.4940015      .7265455  1.555095  27.54811
1999 1.4446623     1.1652954 1.5504937  29.44548
2000  1.624464     1.2169383 1.5403112 30.923504
2001 1.8489385     1.6239994 1.6014005  32.76907
2002 1.7842977     2.1548603 1.6343795 34.658844
2003 1.5015388      2.547112 1.4651107  33.71234
2004   1.31984      2.513441 1.3499476 33.143703
2005   1.26242     2.1621165  1.242377 32.520264
2006   1.28434     1.9499514 1.1495528 34.242355
2007 1.1499054      1.758715 1.0808864 32.320175
2008 1.1527679     1.6857247  1.058693 35.482944
2009 1.2662634     1.9347336 1.1400007  43.93196
2010 1.0901595     1.7592267 1.0301127  45.72581
2011  .9708448     1.7292553  .9869114  49.24996
2012  .9642726     2.0848985   .991586  60.39237
2013 1.0442442     2.4091325  1.016815 72.489685
2014 1.1278877      2.750871 1.0935117  79.23273
2015 1.3721024     4.2357297 1.2785072   87.2698
2016 1.3868723      4.773291  1.327514  94.73785
2017 1.3427736      4.419472 1.2930088  92.89115
2018  1.370266      5.120229 1.2887018  98.98334
2019  1.469829      5.631801 1.3212835 103.83585
2020 1.4790536      7.504243 1.3287678 115.07817
end