Combining Tables using Collect: Rowname variable names don’t match, but contain the same values, so should be 'matchable'

[Johannes Heilmann]

I apologize in advance if this is completely obvious, however I just can’t seem to figure it out:

I’m using a Structural Gravity Model to analyze the effects of a trade agreement. In the end, I get different results for changes in exports, imports and domestic trade in separate tables.

I‘m trying to display all of these in one table, rather than in three separate ones, and I would prefer to do this within Stata rather than in Excel/…

The problem is, that the variable names don’t match: for exports and domestic trade, I use 'exp' to get the results, for imports I use 'imp'.

The values (country names) within those are all the same, so I believe it should be possible to match up these results with the collect command somehow? I just can’t seem to figure out how to do so.

The table commands generating my three separate results tables are as follows:

Exports: table exp if exp!=imp [aweight = BSL] , stat(mean CGeffect) name(exports)

Imports: table imp if exp!=imp [aweight = BSL] , stat(mean CGeffect) name(imports)

Domestic: table exp if exp==imp [aweight = BSL] , stat(mean CGeffect) name(exports)

…when I try to use (a very rudimentary because I’m still struggling) collect layout to combine these, I get the following error: „Your layout specification does not uniquely match any items. …“

I'm sure that i'm missing something obvious, but I’ve gone through the manual so many times now and just can’t seem to figure it out.

I would greatly appreciate any help or tips that point me in the right direction!!

[Jeff Pitblado (StataCorp)]

Without a dataset to play with and example output with an explanation what is not working, it is hard to make any suggestions.

1. I assume your third call to table uses option name(domestic) or something other than name(exports) since that collection name is used in the first call to table.
2. I assume there is a call to collect combine that combines these three collections into one; however, you do not show us your call to collect layout that is giving you trouble.

I suspect the sticking point here is that the second table uses variable imp, whose categories are comparable with that of variable exp but the items are tagged using dimension imp instead of exp. You can't match levels between dimensions, you are going to have to remap one dimension name to the other.

After you collect combine your collections, use collect remap imp = exp to force all the imports items to be tagged so that their levels match up with the exports and domestic items. Then I assume your layout would them be collect layout (exp) (collection#result).

Here is a toy example where I tried to simulate a dataset based on the above description.

set seed 18
set obs 100

gen int exp = runiformint(1,5)
gen int imp = runiformint(1,5)
gen GCeffect = rnormal()
gen BSL = runiformint(10,20)

table exp if exp!=imp [aw=BSL], stat(mean GCeffect) name(exports)
table imp if exp!=imp [aw=BSL], stat(mean GCeffect) name(imports)
table exp if exp==imp [aw=BSL], stat(mean GCeffect) name(domestic)

collect combine all = exports imports domestic

* remap imp to exp
collect remap imp = exp

collect layout (exp) (collection#result)

Here is the resulting table.

. collect layout (exp) (collection#result)

Collection: all
      Rows: exp
   Columns: collection#result
   Table 1: 7 x 3

--------------------------------------------
        |              collection           
        |    exports     imports    domestic
        |       Mean        Mean        Mean
--------+-----------------------------------
exp     |                                   
  1     |   .0897154    .4867606    -2.33192
  2     |   .4505518   -.1566026   -.6880426
  3     |  -.5819547    .1515864    .0384471
  4     |   .1262039    -.271279   -.4438274
  5     |   .1323248    .4910434   -.1160903
  Total |   .0758329    .0758329   -.3752046
--------------------------------------------

[Johannes Heilmann]

YOU. ARE. MY. HERO! Thank you so so so much, that's exactly what I was trying to do!

...I don't know how I missed the collect remap command, I knew that was what I needed to do, but I didn't know how.


Yes, my apologies! To be honest, I was too embarassed by my existing collect layout command to include it. I should have just jumped over my shadow and should have included the code + the dataset from the start. I'll do better next time

Either way, thank you so much for the help!! It is very much appreciated.