Dear all,

I am currently attempting the following task. I have a country-product-year dataset (91 countries, 27 years from 1991-2016) which is unbalanced and contains data on prices and quantities of each item. It looks as follows (dataset is large so I give country-specific examples):

Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input str4 iso3code int(year itemcode) float p double q
"ARG" 1991   15   84.0463 11036600
"ARG" 1991   27    352.53   347600
"ARG" 1991   44  74.31699   573360
"ARG" 1991   56   89.8857  7684800
"ARG" 1991   71        85    46000
"ARG" 1991   75   85.2388   610000
"ARG" 1991   79  59.32511   136000
"ARG" 1991   83   68.9007  2252400
"ARG" 1991  101  162.1438    42200
"ARG" 1991  108        68    15000
"ARG" 1991  116  79.22397  1749887
"ARG" 1991  122  153.6256   289000
"ARG" 1991  125 136.40302   150000
"ARG" 1991  156        10 18200000
"ARG" 1991  176     516.8   241578
"ARG" 1991  181       170    10000
"ARG" 1991  187   175.762    33200
"ARG" 1991  191       177     2000
"ARG" 1991  201   357.398    25800
"ARG" 1991  210       150      100
"ARG" 1991  221       350      430
"ARG" 1991  222       370     8000
"ARG" 1991  236  165.5112 10862000
"ARG" 1991  242   462.823   310600
"ARG" 1991  260       517   121000
"ARG" 1991  267   133.398  4033400
"ARG" 1991  270       140    17000
"ARG" 1991  275        60    58650
"ARG" 1991  280       133    16500
"ARG" 1991  328    288.99   789400
"ARG" 1991  329     49.13   429600
"ARG" 1991  333   127.807   456800
"ARG" 1991  366  455.9952    71579
"ARG" 1991  367     722.4     4800
"ARG" 1991  388       185   716000
"ARG" 1991  394      76.9   362000
"ARG" 1991  401     336.7    92000
"ARG" 1991  403       179   498450
"ARG" 1991  406       500    74000
"ARG" 1991  414       100     3100
"ARG" 1991  417  362.6322    23474
"ARG" 1991  423       369    38500
"ARG" 1991  426      48.8   217000
"ARG" 1991  463        50   627000
"ARG" 1991  486     153.6   194200
"ARG" 1991  490       245   773900
"ARG" 1991  495       295   550200
"ARG" 1991  497       238   656000
"ARG" 1991  507   169.615   203900
"ARG" 1991  515   102.083  1067500
"ARG" 1991  521   117.396   297830
"ARG" 1991  523     122.4    21000
"ARG" 1991  526     449.3    18900
"ARG" 1991  531    2610.8     5600
"ARG" 1991  534     733.8   240000
"ARG" 1991  536    1215.8    52100
"ARG" 1991  544     907.6     7700
"ARG" 1991  560  395.7692  2081620
"ARG" 1991  567  110.0835   126000
"ARG" 1991  568  200.5408    67000
"ARG" 1991  569  811.7377      911
"ARG" 1991  571  199.2125     1600
"ARG" 1991  572  199.2126     3100
"ARG" 1991  574  183.8859     3558
"ARG" 1991  600 184.45625     1600
"ARG" 1991  667  47.50001    46075
"ARG" 1991  671     576.7   160761
"ARG" 1991  677    1264.1      310
"ARG" 1991  689       540     2900
"ARG" 1991  711   1806.53     2400
"ARG" 1991  723       440     1200
"ARG" 1991  767      1369   323600
"ARG" 1991  773     136.9     1700
"ARG" 1991  821     136.9     1300
"ARG" 1991  826      1010    94504
"ARG" 1991  867  1499.004  2918000
"ARG" 1991  882  126.7586  6121000
"ARG" 1991  944  1499.004  2919280
"ARG" 1991  977   1990.17    84800
"ARG" 1991  987   800.806   125000
"ARG" 1991 1012   1990.17    84700
"ARG" 1991 1017   1753.24     6732
"ARG" 1991 1032 1753.2814     6732
"ARG" 1991 1035   623.802   141585
"ARG" 1991 1055   623.802   141497
"ARG" 1991 1058    1265.6   373549
"ARG" 1991 1062   818.254   297830
"ARG" 1991 1069  665.9197     5855
"ARG" 1991 1070  665.9197     5855
"ARG" 1991 1073 552.58765      519
"ARG" 1991 1077 552.58765      519
"ARG" 1991 1080  543.1813    30921
"ARG" 1991 1087  543.1813    30921
"ARG" 1991 1094    1265.6   361350
"ARG" 1991 1097  667.2581    45600
"ARG" 1991 1120  667.2632    44813
"ARG" 1991 1141    1312.8     7040
"ARG" 1991 1144    1312.8     7018
"ARG" 1991 1163    1265.6    42000
"ARG" 1991 1182  720.2191    54000
end



Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input str4 iso3code int(year itemcode) float p double q
"BRA" 1991   15   .01541883   2916823
"BRA" 1991   27  .029132357   9488007
"BRA" 1991   44   .02844604    111650
"BRA" 1991   56  .017183634  23624340
"BRA" 1991   71   .02791878      6304
"BRA" 1991   75  .017992996    230423
"BRA" 1991   83   .01494276    257516
"BRA" 1991   89   .01493617     47000
"BRA" 1991  116  .032063466   2267035
"BRA" 1991  122   .01958768    622432
"BRA" 1991  125  .005692714  24537505
"BRA" 1991  137  .001635489    216449
"BRA" 1991  156 .0013970905 260887893
"BRA" 1991  176   .06492159   2744711
"BRA" 1991  181   .04172147     29649
"BRA" 1991  187   .06263688      4566
"BRA" 1991  216   .02296445     35838
"BRA" 1991  217   .07183072    185965
"BRA" 1991  222   .08170213      3525
"BRA" 1991  234  .036363635       220
"BRA" 1991  236   .02401203  14937806
"BRA" 1991  242   .04923585    140548
"BRA" 1991  249   .04976159   1276547
"BRA" 1991  254  .005937624    525968
"BRA" 1991  256  .013492499    198703
"BRA" 1991  257    .0269814     69900
"BRA" 1991  265  .018414844    129678
"BRA" 1991  267  .021085715     35000
"BRA" 1991  270   .02377778      9000
"BRA" 1991  275  .007901669      2278
"BRA" 1991  289   .04416667     12000
"BRA" 1991  328   .04568672   2079751
"BRA" 1991  329         .01   1310000
"BRA" 1991  333       .0249     20000
"BRA" 1991  339   .02417072     13777
"BRA" 1991  388   .04773081   2343811
"BRA" 1991  403  .029506786    887728
"BRA" 1991  406   .17351024     85165
"BRA" 1991  463   .01723641   1950000
"BRA" 1991  486  .015177518   5762141
"BRA" 1991  490  .025130564  18936344
"BRA" 1991  495   .02194331    660657
"BRA" 1991  497  .036265902    436057
"BRA" 1991  507   .02545098     51000
"BRA" 1991  515   .03843395    526904
"BRA" 1991  521    .0462519     16475
"BRA" 1991  523  .006391347      6102
"BRA" 1991  534  .074178666     96672
"BRA" 1991  544   .08181818      2200
"BRA" 1991  560  .021938935    648026
"BRA" 1991  567  .021193936    432435
"BRA" 1991  568   .05748722     64136
"BRA" 1991  569   .07383386     23282
"BRA" 1991  571   .02271236    550053
"BRA" 1991  572   .06486438    111340
"BRA" 1991  574  .023184774   1190307
"BRA" 1991  587   .06298519     47662
"BRA" 1991  591  .006801333   1500000
"BRA" 1991  600  .008040813    643716
"BRA" 1991  603  .006801777    475317
"BRA" 1991  656   .05642464   1520382
"BRA" 1991  661   .12393174    320967
"BRA" 1991  667  .015015882     10389
"BRA" 1991  671  .026431374    166431
"BRA" 1991  687   .11173218     83906
"BRA" 1991  767         .14    686000
"BRA" 1991  780   .03088102      3303
"BRA" 1991  782   .06102278     11635
"BRA" 1991  788   .09788723      7999
"BRA" 1991  789  .015612632    233721
"BRA" 1991  821  .016364582     74979
"BRA" 1991  826   .10329096    413831
"BRA" 1991  836   .09310786     48374
"BRA" 1991  839  .034623217       491
"BRA" 1991  867   .20955773   4510800
"BRA" 1991  882  .029630193  15546642
"BRA" 1991  944   .20955777   4506824
"BRA" 1991  977   .24879746     79000
"BRA" 1991  987    .2908874     29300
"BRA" 1991 1012   .24879795     79032
"BRA" 1991 1017    .1636286     35000
"BRA" 1991 1020  .029551463    262119
"BRA" 1991 1032    .1636286     35000
"BRA" 1991 1035     .160595   1200000
"BRA" 1991 1055    .1605946   1199785
"BRA" 1991 1058   .14680062   2627700
"BRA" 1991 1062   .10993301   1315019
"BRA" 1991 1069   .14906645     19924
"BRA" 1991 1070   .14906645     19924
"BRA" 1991 1080   .15272714     56231
"BRA" 1991 1087   .15272714     56231
"BRA" 1991 1091  .034346152     26000
"BRA" 1991 1094   .14680068   2629150
"BRA" 1991 1097   .14689174     12322
"BRA" 1991 1120   .14688045     12037
"BRA" 1991 1141    .1128395      4050
"BRA" 1991 1144    .1128395      4050
"BRA" 1991 1182    .3544033     18668
"BRA" 1991 1185    .3160016     17117
"BRA" 1992   15    .2082048   2795598
end
As the data example shows, for Argentina I have data for the itemcode 79, but for Brazil I do not. In contrast, Brazil has data for the itemcode 89, while Argentina does not. I would like to transform my dataset such that Brazil also has the itemcode 79 as identifier and Argentina the itemcode 89, logically then with a missing value for price and quantity. In other words, I would like my dataset to contain for each country, the maximum number of items for which there is data in my whole dataset. This would mean that if the max number of items that a country has data on in my dataset(can be missing as well) is 100, then my dataset should have in total 91(# of countries)*27(# of years)*100(# of items)= 245700 observations. All countries should have the same number of items in the dataset, but then with missing data for price and quantity if they do not produce or report data on these items. I hope my question was clear enough with this example, and I would be grateful if anyone here could assist me with this task.

Thank you in advance.

Best,

Satya