Creating a balanced dataset

[satya otil]

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):

* 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

* 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

[Carlo Lazzaro]

Satya:
I fail to get why you want to do that.
By default Stata will omit all the observations with missing values in any variable. ù
Hence, if you have, say, article #79for a given country and year, with missing values in any of the other variables for the same observations, Stata will omit it.
Please also consider that Stata can handle both unbalanced and balanced panl datasets with no problem.

[satya otil]

Hi Carlo,

Sorry for not posting my question clear enough. Let's say we have a set of 91 countries, and each country produces a number of items. Some of the items that country A produces will be produced in country B, while there are items that country B produces but country A does not. Consequently, there will be no observations for some items in country A (itemcode 89) and country B (itemcode 79). You are right, Stata will omit all observations with missing values in any variable. What I would like to do is essentially what the tsfill, full command does for years, but then for items. For example, let's say country A is the country that produces the most items. Country B might not produce some of the items that country A produces, but I would still like to generate a new observation for country B for this item (for example generate itemcode==79 for country B, etc). Then the value for price and quantity will be zero for this item for country B, because there is no data on this item, but that is okay for my purpose. Similarly, any item that country B produces but country A does not I would still like to generate these item observations for country A (for example generate itemcode==89). Ultimately, I just would like this dataset to be structured such that each country has the same number of item observations, also so that I can see which items are produced in which country.

With this question I hope I could explain a bit better to you what I am attempting to do Carlo.

Best,

Satya

[Carlo Lazzaro]

Satya:
thanks for clarifying,
A probably drammatic cumbersome fix is to create a -dta with all the items and the -merge- it with a copy of the original dataset (keeping the master of the original dataset in a safe folder/directory).

[satya otil]

Hi Carlo,

I think actually that is a clever idea, it had not even crossed my mind, now I feel a bit silly. But thank you very much, this should work!

Best,

Satya

[Carlo Lazzaro]

Satya:
that's one of the most precious advantage of this forum: comparing ideas.
That is the substantive reason why FAQ recommend to post on the forums and not privately: many potentially interested listers are more likely to give helpful replies than a private message reader.

[satya otil]

Hi Carlo,

That is very true, and I am one to say I am learning much from these discussions, so thank you for your input. I am currently trying out your idea, but for a reason unknown to me it is not giving me the desired result. I created a dataset with all the items first:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int itemcode
  15
  27
  44
  56
  71
  75
  79
  83
  89
  92
  94
  97
 101
 103
 108
 116
 122
 125
 135
 136
 137
 149
 156
 157
 161
 176
 181
 187
 191
 195
 197
 201
 203
 205
 210
 211
 216
 217
 220
 221
 222
 223
 224
 225
 226
 234
 236
 242
 249
 254
 256
 257
 260
 263
 265
 267
 270
 275
 277
 280
 289
 292
 296
 299
 305
 310
 311
 328
 329
 333
 336
 339
 358
 366
 367
 372
 373
 378
 388
 393
 394
 397
 399
 401
 402
 403
 406
 407
 414
 417
 420
 423
 426
 430
 446
 449
 459
 461
 463
 486
 489
 490
 495
 497
 507
 512
 515
 521
 523
 526
 530
 531
 534
 536
 541
 544
 547
 549
 550
 552
 554
 558
 560
 567
 568
 569
 571
 572
 574
 577
 587
 591
 592
 600
 603
 619
 656
 661
 667
 671
 677
 687
 689
 692
 693
 698
 702
 711
 720
 723
 748
 754
 767
 773
 777
 778
 780
 782
 788
 789
 800
 809
 813
 821
 826
 836
 839
 867
 882
 919
 944
 947
 951
 957
 972
 977
 982
 987
 995
 999
1012
1017
1020
1025
1032
1035
1055
1058
1062
1069
1070
1073
1077
1080
1084
1087
1089
1091
1094
1097
1100
1108
1111
1120
1122
1124
1127
1130
1137
1141
1144
1151
1154
1158
1161
1163
1166
1167
1176
1182
1183
1185
end

and merged this dataset with my original dataset using the following code:

 merge 1:m itemcode using "original_dataset.dta"

But this code does not give me the dataset that I desire, namely one where each country has 222 item observations per year. If I give an example using the command tab itemcode if year == 2010:

   itemcode |      Freq.     Percent        Cum.
------------+-----------------------------------
         15 |        124        0.86        0.86
         27 |        122        0.85        1.70
         44 |        107        0.74        2.45
         56 |        167        1.16        3.60
         71 |         64        0.44        4.05
         75 |         77        0.53        4.58
         79 |         86        0.60        5.18
         83 |        112        0.78        5.95
         89 |         31        0.21        6.17
         92 |          3        0.02        6.19
         94 |         10        0.07        6.26
         97 |         40        0.28        6.53
        101 |         12        0.08        6.62
        103 |         28        0.19        6.81
        108 |         60        0.42        7.23
        116 |        160        1.11        8.34
        122 |        118        0.82        9.15
        125 |        102        0.71        9.86
        135 |         14        0.10        9.96
        136 |         50        0.35       10.30
        137 |         59        0.41       10.71
        149 |         83        0.58       11.29
        156 |        111        0.77       12.06
        157 |         65        0.45       12.51
        161 |          3        0.02       12.53
        176 |        128        0.89       13.42
        181 |         62        0.43       13.85
        187 |         97        0.67       14.52
        191 |         58        0.40       14.92
        195 |         40        0.28       15.20
        197 |         24        0.17       15.36
        201 |         57        0.39       15.76
        203 |          6        0.04       15.80
        205 |         40        0.28       16.08
        210 |         27        0.19       16.26
        211 |        114        0.79       17.05
        216 |          6        0.04       17.10
        217 |         35        0.24       17.34
        220 |         29        0.20       17.54
        221 |         48        0.33       17.87
        222 |         56        0.39       18.26
        223 |         22        0.15       18.41
        224 |          7        0.05       18.46
        225 |         33        0.23       18.69
        226 |         12        0.08       18.77
        234 |         72        0.50       19.27
        236 |        103        0.71       19.98
        242 |        116        0.80       20.79
        249 |         95        0.66       21.45
        254 |         44        0.30       21.75
        256 |         45        0.31       22.06
        257 |         46        0.32       22.38
        260 |         40        0.28       22.66
        263 |          7        0.05       22.71
        265 |         45        0.31       23.02
        267 |         74        0.51       23.53
        270 |         64        0.44       23.98
        275 |          7        0.05       24.02
        277 |          1        0.01       24.03
        280 |         24        0.17       24.20
        289 |         74        0.51       24.71
        292 |         29        0.20       24.91
        296 |         17        0.12       25.03
        299 |         17        0.12       25.15
        305 |          1        0.01       25.15
        310 |          2        0.01       25.17
        311 |          2        0.01       25.18
        328 |        100        0.69       25.87
        329 |        100        0.69       26.57
        333 |         58        0.40       26.97
        336 |         16        0.11       27.08
        339 |         63        0.44       27.52
        358 |        152        1.05       28.57
        366 |         33        0.23       28.80
        367 |         46        0.32       29.12
        372 |        105        0.73       29.85
        373 |         63        0.44       30.28
        378 |          2        0.01       30.30
        388 |        175        1.21       31.51
        393 |         94        0.65       32.16
        394 |        117        0.81       32.97
        397 |        136        0.94       33.91
        399 |         93        0.64       34.56
        401 |        122        0.85       35.40
        402 |         63        0.44       35.84
        403 |        142        0.98       36.82
        406 |         99        0.69       37.51
        407 |         56        0.39       37.90
        414 |        111        0.77       38.67
        417 |         87        0.60       39.27
        420 |         60        0.42       39.69
        423 |         19        0.13       39.82
        426 |        132        0.91       40.73
        430 |         46        0.32       41.05
        446 |         52        0.36       41.41
        449 |         72        0.50       41.91
        459 |         20        0.14       42.05
        461 |         16        0.11       42.16
        463 |        200        1.39       43.55
        486 |        134        0.93       44.47
        489 |         53        0.37       44.84
        490 |        121        0.84       45.68
        495 |         74        0.51       46.19
        497 |        108        0.75       46.94
        507 |         79        0.55       47.49
        512 |         75        0.52       48.01
        515 |         96        0.67       48.67
        521 |         88        0.61       49.28
        523 |         54        0.37       49.66
        526 |         70        0.49       50.14
        530 |         33        0.23       50.37
        531 |         69        0.48       50.85
        534 |         83        0.58       51.42
        536 |         87        0.60       52.03
        541 |         35        0.24       52.27
        542 |          2        0.01       52.28
        544 |         78        0.54       52.82
        547 |         45        0.31       53.14
        549 |         21        0.15       53.28
        550 |         37        0.26       53.54
        552 |         25        0.17       53.71
        554 |         14        0.10       53.81
        558 |         61        0.42       54.23
        560 |         93        0.64       54.87
        567 |        119        0.82       55.70
        568 |         97        0.67       56.37
        569 |         52        0.36       56.73
        571 |        102        0.71       57.44
        572 |         71        0.49       57.93
        574 |         90        0.62       58.55
        577 |         38        0.26       58.82
        587 |         17        0.12       58.94
        591 |          4        0.03       58.96
        592 |         23        0.16       59.12
        600 |         69        0.48       59.60
        603 |         75        0.52       60.12
        619 |        168        1.16       61.28
        656 |         84        0.58       61.87
        661 |         62        0.43       62.30
        667 |         49        0.34       62.64
        671 |          3        0.02       62.66
        677 |         33        0.23       62.89
        687 |         44        0.30       63.19
        689 |         69        0.48       63.67
        692 |         17        0.12       63.79
        693 |         11        0.08       63.86
        698 |          9        0.06       63.92
        702 |         21        0.15       64.07
        711 |         44        0.30       64.38
        720 |         39        0.27       64.65
        723 |         74        0.51       65.16
        748 |          9        0.06       65.22
        754 |         12        0.08       65.30
        767 |         99        0.69       65.99
        773 |         35        0.24       66.23
        777 |         23        0.16       66.39
        778 |          2        0.01       66.41
        780 |         20        0.14       66.54
        782 |         24        0.17       66.71
        788 |          8        0.06       66.77
        789 |         25        0.17       66.94
        800 |          9        0.06       67.00
        809 |          7        0.05       67.05
        813 |          8        0.06       67.11
        821 |         27        0.19       67.29
        826 |        137        0.95       68.24
        836 |         35        0.24       68.48
        839 |          4        0.03       68.51
        867 |        205        1.42       69.93
        882 |        195        1.35       71.28
        919 |        205        1.42       72.70
        944 |        205        1.42       74.13
        947 |         29        0.20       74.33
        951 |         23        0.16       74.49
        957 |         29        0.20       74.69
        972 |         29        0.20       74.89
        977 |        189        1.31       76.20
        982 |         80        0.55       76.75
        987 |        100        0.69       77.44
        995 |        188        1.30       78.75
        999 |         50        0.35       79.09
       1012 |        189        1.31       80.40
       1017 |        180        1.25       81.65
       1020 |        110        0.76       82.41
       1025 |        175        1.21       83.63
       1032 |        178        1.23       84.86
       1035 |        189        1.31       86.17
       1055 |        188        1.30       87.47
       1058 |        206        1.43       88.90
       1062 |        207        1.43       90.33
       1069 |         78        0.54       90.87
       1070 |         77        0.53       91.41
       1073 |         41        0.28       91.69
       1077 |         39        0.27       91.96
       1080 |         65        0.45       92.41
       1084 |          6        0.04       92.45
       1087 |         63        0.44       92.89
       1089 |         11        0.08       92.97
       1091 |         50        0.35       93.31
       1094 |        206        1.43       94.74
       1097 |         78        0.54       95.28
       1100 |          2        0.01       95.29
       1108 |          8        0.06       95.35
       1111 |          2        0.01       95.36
       1120 |         78        0.54       95.90
       1122 |          8        0.06       95.96
       1124 |          2        0.01       95.97
       1127 |         36        0.25       96.22
       1130 |         27        0.19       96.41
       1137 |         35        0.24       96.65
       1141 |         59        0.41       97.06
       1144 |         58        0.40       97.46
       1151 |          2        0.01       97.48
       1154 |          2        0.01       97.49
       1158 |          2        0.01       97.51
       1161 |          2        0.01       97.52
       1163 |         60        0.42       97.94
       1166 |         62        0.43       98.36
       1167 |          9        0.06       98.43
       1176 |          4        0.03       98.45
       1182 |        137        0.95       99.40
       1183 |         55        0.38       99.79
       1185 |         31        0.21      100.00
------------+-----------------------------------

So I do not know what I am doing wrong with the merging. I assume this is what you meant with the merging idea right?

Best,

Satya

[Carlo Lazzaro]

Satya:
what if:

. merge 1:1 itemcode using "C:\Users\user\Desktop\item_code.dta"

    Result                           # of obs.
    -----------------------------------------
    not matched                           122
        from master                         0  (_merge==1)
        from using                        122  (_merge==2)

    matched                               100  (_merge==3)
    -----------------------------------------

. list

     +-------------------------------------------------------------------+
     | iso3code   year   itemcode          p          q           _merge |
     |-------------------------------------------------------------------|
  1. |      ARG   1991         15    84.0463   11036600      matched (3) |
  2. |      ARG   1991         27     352.53     347600      matched (3) |
  3. |      ARG   1991         44   74.31699     573360      matched (3) |
  4. |      ARG   1991         56    89.8857    7684800      matched (3) |
  5. |      ARG   1991         71         85      46000      matched (3) |
     |-------------------------------------------------------------------|
  6. |      ARG   1991         75    85.2388     610000      matched (3) |
  7. |      ARG   1991         79   59.32511     136000      matched (3) |
  8. |      ARG   1991         83    68.9007    2252400      matched (3) |
  9. |      ARG   1991        101   162.1438      42200      matched (3) |
 10. |      ARG   1991        108         68      15000      matched (3) |
     |-------------------------------------------------------------------|
 11. |      ARG   1991        116   79.22397    1749887      matched (3) |
 12. |      ARG   1991        122   153.6256     289000      matched (3) |
 13. |      ARG   1991        125    136.403     150000      matched (3) |
 14. |      ARG   1991        156         10   18200000      matched (3) |
 15. |      ARG   1991        176      516.8     241578      matched (3) |
     |-------------------------------------------------------------------|
 16. |      ARG   1991        181        170      10000      matched (3) |
 17. |      ARG   1991        187    175.762      33200      matched (3) |
 18. |      ARG   1991        191        177       2000      matched (3) |
 19. |      ARG   1991        201    357.398      25800      matched (3) |
 20. |      ARG   1991        210        150        100      matched (3) |
     |-------------------------------------------------------------------|
 21. |      ARG   1991        221        350        430      matched (3) |
 22. |      ARG   1991        222        370       8000      matched (3) |
 23. |      ARG   1991        236   165.5112   10862000      matched (3) |
 24. |      ARG   1991        242    462.823     310600      matched (3) |
 25. |      ARG   1991        260        517     121000      matched (3) |
     |-------------------------------------------------------------------|
 26. |      ARG   1991        267    133.398    4033400      matched (3) |
 27. |      ARG   1991        270        140      17000      matched (3) |
 28. |      ARG   1991        275         60      58650      matched (3) |
 29. |      ARG   1991        280        133      16500      matched (3) |
 30. |      ARG   1991        328     288.99     789400      matched (3) |
     |-------------------------------------------------------------------|
 31. |      ARG   1991        329      49.13     429600      matched (3) |
 32. |      ARG   1991        333    127.807     456800      matched (3) |
 33. |      ARG   1991        366   455.9952      71579      matched (3) |
 34. |      ARG   1991        367      722.4       4800      matched (3) |
 35. |      ARG   1991        388        185     716000      matched (3) |
     |-------------------------------------------------------------------|
 36. |      ARG   1991        394       76.9     362000      matched (3) |
 37. |      ARG   1991        401      336.7      92000      matched (3) |
 38. |      ARG   1991        403        179     498450      matched (3) |
 39. |      ARG   1991        406        500      74000      matched (3) |
 40. |      ARG   1991        414        100       3100      matched (3) |
     |-------------------------------------------------------------------|
 41. |      ARG   1991        417   362.6322      23474      matched (3) |
 42. |      ARG   1991        423        369      38500      matched (3) |
 43. |      ARG   1991        426       48.8     217000      matched (3) |
 44. |      ARG   1991        463         50     627000      matched (3) |
 45. |      ARG   1991        486      153.6     194200      matched (3) |
     |-------------------------------------------------------------------|
 46. |      ARG   1991        490        245     773900      matched (3) |
 47. |      ARG   1991        495        295     550200      matched (3) |
 48. |      ARG   1991        497        238     656000      matched (3) |
 49. |      ARG   1991        507    169.615     203900      matched (3) |
 50. |      ARG   1991        515    102.083    1067500      matched (3) |
     |-------------------------------------------------------------------|
 51. |      ARG   1991        521    117.396     297830      matched (3) |
 52. |      ARG   1991        523      122.4      21000      matched (3) |
 53. |      ARG   1991        526      449.3      18900      matched (3) |
 54. |      ARG   1991        531     2610.8       5600      matched (3) |
 55. |      ARG   1991        534      733.8     240000      matched (3) |
     |-------------------------------------------------------------------|
 56. |      ARG   1991        536     1215.8      52100      matched (3) |
 57. |      ARG   1991        544      907.6       7700      matched (3) |
 58. |      ARG   1991        560   395.7692    2081620      matched (3) |
 59. |      ARG   1991        567   110.0835     126000      matched (3) |
 60. |      ARG   1991        568   200.5408      67000      matched (3) |
     |-------------------------------------------------------------------|
 61. |      ARG   1991        569   811.7377        911      matched (3) |
 62. |      ARG   1991        571   199.2125       1600      matched (3) |
 63. |      ARG   1991        572   199.2126       3100      matched (3) |
 64. |      ARG   1991        574   183.8859       3558      matched (3) |
 65. |      ARG   1991        600   184.4563       1600      matched (3) |
     |-------------------------------------------------------------------|
 66. |      ARG   1991        667   47.50001      46075      matched (3) |
 67. |      ARG   1991        671      576.7     160761      matched (3) |
 68. |      ARG   1991        677     1264.1        310      matched (3) |
 69. |      ARG   1991        689        540       2900      matched (3) |
 70. |      ARG   1991        711    1806.53       2400      matched (3) |
     |-------------------------------------------------------------------|
 71. |      ARG   1991        723        440       1200      matched (3) |
 72. |      ARG   1991        767       1369     323600      matched (3) |
 73. |      ARG   1991        773      136.9       1700      matched (3) |
 74. |      ARG   1991        821      136.9       1300      matched (3) |
 75. |      ARG   1991        826       1010      94504      matched (3) |
     |-------------------------------------------------------------------|
 76. |      ARG   1991        867   1499.004    2918000      matched (3) |
 77. |      ARG   1991        882   126.7586    6121000      matched (3) |
 78. |      ARG   1991        944   1499.004    2919280      matched (3) |
 79. |      ARG   1991        977    1990.17      84800      matched (3) |
 80. |      ARG   1991        987    800.806     125000      matched (3) |
     |-------------------------------------------------------------------|
 81. |      ARG   1991       1012    1990.17      84700      matched (3) |
 82. |      ARG   1991       1017    1753.24       6732      matched (3) |
 83. |      ARG   1991       1032   1753.281       6732      matched (3) |
 84. |      ARG   1991       1035    623.802     141585      matched (3) |
 85. |      ARG   1991       1055    623.802     141497      matched (3) |
     |-------------------------------------------------------------------|
 86. |      ARG   1991       1058     1265.6     373549      matched (3) |
 87. |      ARG   1991       1062    818.254     297830      matched (3) |
 88. |      ARG   1991       1069   665.9197       5855      matched (3) |
 89. |      ARG   1991       1070   665.9197       5855      matched (3) |
 90. |      ARG   1991       1073   552.5876        519      matched (3) |
     |-------------------------------------------------------------------|
 91. |      ARG   1991       1077   552.5876        519      matched (3) |
 92. |      ARG   1991       1080   543.1813      30921      matched (3) |
 93. |      ARG   1991       1087   543.1813      30921      matched (3) |
 94. |      ARG   1991       1094     1265.6     361350      matched (3) |
 95. |      ARG   1991       1097   667.2581      45600      matched (3) |
     |-------------------------------------------------------------------|
 96. |      ARG   1991       1120   667.2632      44813      matched (3) |
 97. |      ARG   1991       1141     1312.8       7040      matched (3) |
 98. |      ARG   1991       1144     1312.8       7018      matched (3) |
 99. |      ARG   1991       1163     1265.6      42000      matched (3) |
100. |      ARG   1991       1182   720.2191      54000      matched (3) |
     |-------------------------------------------------------------------|
101. |               .         89          .          .   using only (2) |
102. |               .         92          .          .   using only (2) |
103. |               .         94          .          .   using only (2) |
104. |               .         97          .          .   using only (2) |
105. |               .        103          .          .   using only (2) |
     |-------------------------------------------------------------------|
106. |               .        135          .          .   using only (2) |
107. |               .        136          .          .   using only (2) |
108. |               .        137          .          .   using only (2) |
109. |               .        149          .          .   using only (2) |
110. |               .        157          .          .   using only (2) |
     |-------------------------------------------------------------------|
111. |               .        161          .          .   using only (2) |
112. |               .        195          .          .   using only (2) |
113. |               .        197          .          .   using only (2) |
114. |               .        203          .          .   using only (2) |
115. |               .        205          .          .   using only (2) |
     |-------------------------------------------------------------------|
116. |               .        211          .          .   using only (2) |
117. |               .        216          .          .   using only (2) |
118. |               .        217          .          .   using only (2) |
119. |               .        220          .          .   using only (2) |
120. |               .        223          .          .   using only (2) |
     |-------------------------------------------------------------------|
121. |               .        224          .          .   using only (2) |
122. |               .        225          .          .   using only (2) |
123. |               .        226          .          .   using only (2) |
124. |               .        234          .          .   using only (2) |
125. |               .        249          .          .   using only (2) |
     |-------------------------------------------------------------------|
126. |               .        254          .          .   using only (2) |
127. |               .        256          .          .   using only (2) |
128. |               .        257          .          .   using only (2) |
129. |               .        263          .          .   using only (2) |
130. |               .        265          .          .   using only (2) |
     |-------------------------------------------------------------------|
131. |               .        277          .          .   using only (2) |
132. |               .        289          .          .   using only (2) |
133. |               .        292          .          .   using only (2) |
134. |               .        296          .          .   using only (2) |
135. |               .        299          .          .   using only (2) |
     |-------------------------------------------------------------------|
136. |               .        305          .          .   using only (2) |
137. |               .        310          .          .   using only (2) |
138. |               .        311          .          .   using only (2) |
139. |               .        336          .          .   using only (2) |
140. |               .        339          .          .   using only (2) |
     |-------------------------------------------------------------------|
141. |               .        358          .          .   using only (2) |
142. |               .        372          .          .   using only (2) |
143. |               .        373          .          .   using only (2) |
144. |               .        378          .          .   using only (2) |
145. |               .        393          .          .   using only (2) |
     |-------------------------------------------------------------------|
146. |               .        397          .          .   using only (2) |
147. |               .        399          .          .   using only (2) |
148. |               .        402          .          .   using only (2) |
149. |               .        407          .          .   using only (2) |
150. |               .        420          .          .   using only (2) |
     |-------------------------------------------------------------------|
151. |               .        430          .          .   using only (2) |
152. |               .        446          .          .   using only (2) |
153. |               .        449          .          .   using only (2) |
154. |               .        459          .          .   using only (2) |
155. |               .        461          .          .   using only (2) |
     |-------------------------------------------------------------------|
156. |               .        489          .          .   using only (2) |
157. |               .        512          .          .   using only (2) |
158. |               .        530          .          .   using only (2) |
159. |               .        541          .          .   using only (2) |
160. |               .        547          .          .   using only (2) |
     |-------------------------------------------------------------------|
161. |               .        549          .          .   using only (2) |
162. |               .        550          .          .   using only (2) |
163. |               .        552          .          .   using only (2) |
164. |               .        554          .          .   using only (2) |
165. |               .        558          .          .   using only (2) |
     |-------------------------------------------------------------------|
166. |               .        577          .          .   using only (2) |
167. |               .        587          .          .   using only (2) |
168. |               .        591          .          .   using only (2) |
169. |               .        592          .          .   using only (2) |
170. |               .        603          .          .   using only (2) |
     |-------------------------------------------------------------------|
171. |               .        619          .          .   using only (2) |
172. |               .        656          .          .   using only (2) |
173. |               .        661          .          .   using only (2) |
174. |               .        687          .          .   using only (2) |
175. |               .        692          .          .   using only (2) |
     |-------------------------------------------------------------------|
176. |               .        693          .          .   using only (2) |
177. |               .        698          .          .   using only (2) |
178. |               .        702          .          .   using only (2) |
179. |               .        720          .          .   using only (2) |
180. |               .        748          .          .   using only (2) |
     |-------------------------------------------------------------------|
181. |               .        754          .          .   using only (2) |
182. |               .        777          .          .   using only (2) |
183. |               .        778          .          .   using only (2) |
184. |               .        780          .          .   using only (2) |
185. |               .        782          .          .   using only (2) |
     |-------------------------------------------------------------------|
186. |               .        788          .          .   using only (2) |
187. |               .        789          .          .   using only (2) |
188. |               .        800          .          .   using only (2) |
189. |               .        809          .          .   using only (2) |
190. |               .        813          .          .   using only (2) |
     |-------------------------------------------------------------------|
191. |               .        836          .          .   using only (2) |
192. |               .        839          .          .   using only (2) |
193. |               .        919          .          .   using only (2) |
194. |               .        947          .          .   using only (2) |
195. |               .        951          .          .   using only (2) |
     |-------------------------------------------------------------------|
196. |               .        957          .          .   using only (2) |
197. |               .        972          .          .   using only (2) |
198. |               .        982          .          .   using only (2) |
199. |               .        995          .          .   using only (2) |
200. |               .        999          .          .   using only (2) |
     |-------------------------------------------------------------------|
201. |               .       1020          .          .   using only (2) |
202. |               .       1025          .          .   using only (2) |
203. |               .       1084          .          .   using only (2) |
204. |               .       1089          .          .   using only (2) |
205. |               .       1091          .          .   using only (2) |
     |-------------------------------------------------------------------|
206. |               .       1100          .          .   using only (2) |
207. |               .       1108          .          .   using only (2) |
208. |               .       1111          .          .   using only (2) |
209. |               .       1122          .          .   using only (2) |
210. |               .       1124          .          .   using only (2) |
     |-------------------------------------------------------------------|
211. |               .       1127          .          .   using only (2) |
212. |               .       1130          .          .   using only (2) |
213. |               .       1137          .          .   using only (2) |
214. |               .       1151          .          .   using only (2) |
215. |               .       1154          .          .   using only (2) |
     |-------------------------------------------------------------------|
216. |               .       1158          .          .   using only (2) |
217. |               .       1161          .          .   using only (2) |
218. |               .       1166          .          .   using only (2) |
219. |               .       1167          .          .   using only (2) |
220. |               .       1176          .          .   using only (2) |
     |-------------------------------------------------------------------|
221. |               .       1183          .          .   using only (2) |
222. |               .       1185          .          .   using only (2) |
     +-------------------------------------------------------------------+

[satya otil]

Hi Carlo,

If you meant here that I use the original dataset as my master and the dataset which contains the itemcodes as the user dataset, then yes I have tried this as well using your code, but it does not work because I get the following error:

variable itemcode does not uniquely identify observations in the master data
r(459);

Which I understand is due to the fact that a specific item observation occurs across multiple countries and years.

Best,

Satya

[Carlo Lazzaro]

Satya:
the issue here is exactly the one you surmised.
Hence, unless you add country and year identifiers in the using dataset and repeat this procedure for each country, I find difficult to accomplish the task you're after.
Obviously, other listers can hopefully chime in and advise a much more efficient approach.

[satya otil]

Hi Carlo,

Thank you for your reply. I will indeed then try to find another method to accomplish this. Thank you in any case for your help.

Best,

Satya

[satya otil]

Dear Carlo and future readers of this post,

I found the solution, the command that I was looking for was -fillin-.

Best,

Satya

[Carlo Lazzaro]

Satya:
thanks for sharing.
Good take indeed (admittedly, I've never used -fillin- and was barely aware of it).

[satya otil]

Hi Carlo,

No problem, happy to help.

Best,

Satya