Getting Unique Pairs of Industries within a group/category

[Preety Bhogal]

Hi All,
I am working with the input output tables. With (Andrew's) help, I managed to transform the dataset from matrix to long format. However, now I am trying to link BEA industry codes to NAICS and managed to do that mapping.
Since I wan to see how much one industry uses from another and vice-versa, I want to do two things.
1) BEA (code_supplier) to NAICS (naics_supplier) - how many NAICS codes are linked to BEA. For that, I created the tag and wanted variables. However, I want to sum the values column if there is 1 NAICS linked to multiple BEA codes. I am not sure how to achieve that. Is there a way to see such cases?
2) I want to produce values for each unique industry pair and how to see these pairs stacked over one another. For instance - soybean farming supplier (NAICS 111110) supplying to wheat farming user (NAICS 111140) is 455 and vice versa (wheat farming supplying to soybean farming is how much?). I want to produce unique pairwise combinations of buyers and sellers along with their values. I am not sure how to obtain this result. My dataset currently looks like this. I only want to have three columns based on the dataset (using NAICS) buyers, sellers, and value.

* Example generated by -dataex-. For more info, type help dataex
clear
input str6 code_supplier str96 description_supplier str6 code_user str96 description_user str119 naics_title_supplier long naics_supplier str6 naics_user strL naics_title_user long(value total_output_user) byte(tag wanted)
"S00401" "Scrap"                        "4200ID" "Customs duties"  "" . ""       ""                                    0 38513 0 0
"S00300" "Noncomparable imports"        "4200ID" "Customs duties"  "" . ""       ""                                    0 38513 0 0
"S00402" "Used and secondhand goods"    "4200ID" "Customs duties"  "" . ""       ""                                    0 38513 0 0
"S00900" "Rest of the world adjustment" "4200ID" "Customs duties"  "" . ""       ""                                    0 38513 0 0
"4200ID" "Customs duties"               "4200ID" "Customs duties"  "" . ""       ""                                    0 38513 0 0
"4200ID" "Customs duties"               "1111A0" "Oilseed farming" "" . "111110" "Soybean Farming"                     0 38216 0 0
"S00402" "Used and secondhand goods"    "1111A0" "Oilseed farming" "" . "111110" "Soybean Farming"                     0 38216 0 0
"S00900" "Rest of the world adjustment" "1111A0" "Oilseed farming" "" . "111110" "Soybean Farming"                     0 38216 0 0
"S00300" "Noncomparable imports"        "1111A0" "Oilseed farming" "" . "111110" "Soybean Farming"                   111 38216 0 0
"S00401" "Scrap"                        "1111A0" "Oilseed farming" "" . "111110" "Soybean Farming"                     0 38216 0 0
"S00900" "Rest of the world adjustment" "1111A0" "Oilseed farming" "" . "111120" "Oilseed (except Soybean) Farming "   0 38216 0 0
"4200ID" "Customs duties"               "1111A0" "Oilseed farming" "" . "111120" "Oilseed (except Soybean) Farming "   0 38216 0 0
"S00300" "Noncomparable imports"        "1111A0" "Oilseed farming" "" . "111120" "Oilseed (except Soybean) Farming " 111 38216 0 0
"S00401" "Scrap"                        "1111A0" "Oilseed farming" "" . "111120" "Oilseed (except Soybean) Farming "   0 38216 0 0
"S00402" "Used and secondhand goods"    "1111A0" "Oilseed farming" "" . "111120" "Oilseed (except Soybean) Farming "   0 38216 0 0
"4200ID" "Customs duties"               "1111B0" "Grain farming"   "" . "111130" "Dry Pea and Bean Farming "           0 57882 0 0
"S00401" "Scrap"                        "1111B0" "Grain farming"   "" . "111130" "Dry Pea and Bean Farming "           0 57882 0 0
"S00900" "Rest of the world adjustment" "1111B0" "Grain farming"   "" . "111130" "Dry Pea and Bean Farming "           0 57882 0 0
"S00402" "Used and secondhand goods"    "1111B0" "Grain farming"   "" . "111130" "Dry Pea and Bean Farming "           0 57882 0 0
"S00300" "Noncomparable imports"        "1111B0" "Grain farming"   "" . "111130" "Dry Pea and Bean Farming "         371 57882 0 0
end

Thanks for your help in advance!

Preety

[Clyde Schechter]

Sorry, but I'm confused by your data example and explanation. First, the naics_supplier variable contains only missing values, so it does not seem suitable for the purpose you seem to want. Second, you say

For instance - soybean farming supplier (NAICS 111110) supplying to wheat farming user (NAICS 111140) is 455...

But there is no NAICS 111140 in the data, nor can I see any combination of values of the value variable that gives 455 as a result.

Please clarify and provide a data example that contains enough information to work with.

All of that said, on a hunch, have you looked at the -collapse- command? I think it is likely the key to this problem, even though I am baffled by the specifics.

[Preety Bhogal]

Hi Clyde, Sorry for the confusion. I have dropped the missing observations and created another example using dataex.
The dataset is created as follows - code_supplier and code_user both are linked to naics_supplier and naics_user. I want to create unique pairwise combinations of naics_supplier and naics_user with their corresponding values. I looked at the condition command and worked with it. However, I want to see the pairwise combinations of these industries together. For instance - naics_user (111140) is buying from naics_supplier (111110) soybean farming and the value is 455. I want to see the other combination and its value where soybean farming is buying from wheat farming (the other direction).
Please see the dataset that I have after dropping the missing observations. Thanks!

* Example generated by -dataex-. For more info, type help dataex
clear
input str6 code_supplier str96 description_supplier str6 code_user str96 description_user str119 naics_title_supplier long naics_supplier str6 naics_user strL naics_title_user long(value total_output_user) byte(tag wanted)
"1111A0" "Oilseed farming" "4200ID" "Customs duties"              "Soybean Farming" 111110 ""       ""                                                      0 38513 1 2
"1111A0" "Oilseed farming" "1111A0" "Oilseed farming"             "Soybean Farming" 111110 "111110" "Soybean Farming"                                    8332 38216 0 2
"1111A0" "Oilseed farming" "1111A0" "Oilseed farming"             "Soybean Farming" 111110 "111120" "Oilseed (except Soybean) Farming "                  8332 38216 0 2
"1111A0" "Oilseed farming" "1111B0" "Grain farming"               "Soybean Farming" 111110 "111130" "Dry Pea and Bean Farming "                           455 57882 0 2
"1111A0" "Oilseed farming" "1111B0" "Grain farming"               "Soybean Farming" 111110 "111140" "Wheat Farming"                                       455 57882 0 2
"1111A0" "Oilseed farming" "1111B0" "Grain farming"               "Soybean Farming" 111110 "111150" "Corn Farming "                                       455 57882 0 2
"1111A0" "Oilseed farming" "1111B0" "Grain farming"               "Soybean Farming" 111110 "111160" "Rice Farming"                                        455 57882 0 2
"1111A0" "Oilseed farming" "1111B0" "Grain farming"               "Soybean Farming" 111110 "111191" "Oilseed and Grain Combination Farming "              455 57882 0 2
"1111A0" "Oilseed farming" "1111B0" "Grain farming"               "Soybean Farming" 111110 "111199" "All Other Grain Farming "                            455 57882 0 2
"1111A0" "Oilseed farming" "111200" "Vegetable and melon farming" "Soybean Farming" 111110 "111211" "Potato Farming "                                      40 18806 0 2
"1111A0" "Oilseed farming" "111200" "Vegetable and melon farming" "Soybean Farming" 111110 "111219" "Other Vegetable (except Potato) and Melon Farming "   40 18806 0 2
"1111A0" "Oilseed farming" "111300" "Fruit and tree nut farming"  "Soybean Farming" 111110 "111310" "Orange Groves"                                         0 29958 0 2
"1111A0" "Oilseed farming" "111300" "Fruit and tree nut farming"  "Soybean Farming" 111110 "111320" "Citrus (except Orange) Groves "                        0 29958 0 2
"1111A0" "Oilseed farming" "111300" "Fruit and tree nut farming"  "Soybean Farming" 111110 "111331" "Apple Orchards "                                       0 29958 0 2
"1111A0" "Oilseed farming" "111300" "Fruit and tree nut farming"  "Soybean Farming" 111110 "111332" "Grape Vineyards "                                      0 29958 0 2
"1111A0" "Oilseed farming" "111300" "Fruit and tree nut farming"  "Soybean Farming" 111110 "111333" "Strawberry Farming "                                   0 29958 0 2
"1111A0" "Oilseed farming" "111300" "Fruit and tree nut farming"  "Soybean Farming" 111110 "111334" "Berry (except Strawberry) Farming "                    0 29958 0 2
"1111A0" "Oilseed farming" "111300" "Fruit and tree nut farming"  "Soybean Farming" 111110 "111335" "Tree Nut Farming "                                     0 29958 0 2
"1111A0" "Oilseed farming" "111300" "Fruit and tree nut farming"  "Soybean Farming" 111110 "111336" "Fruit and Tree Nut Combination Farming "               0 29958 0 2
"1111A0" "Oilseed farming" "111300" "Fruit and tree nut farming"  "Soybean Farming" 111110 "111339" "Other Noncitrus Fruit Farming "                        0 29958 0 2
end

Regards,
Preety

[Clyde Schechter]

This data set is better, although still not optimal for purpose. After all, you are asking for pairs of suppliers and users, but there is only one supplier in the data shown. Anyway, I think what you are looking for is

collapse (sum) value, by(naics_supplier naics_user)

If that's not right, when posting back, please show an example that produces wrong results with this code, and then explain what the results you want should be.