Sum two rows and other rows with a similar pattern using a condition

[Preety Bhogal]

Hi All,

I am working with a dataset on input and output for buyers and sellers. I have observed a pattern in the dataset and want to calculate the sum of the total output variable (without using collapse) in a new column using a condition. Here is the dataset:

* Example generated by -dataex-. For more info, type help dataex
clear
input str22 code_supplier str96 description_supplier str7 code_user str96 description_user str119 naics_title_supplier str10(naics_supplier naics_user) str119 naics_title_user long value float(total_output_user total_interm_user sup)
"1111A0" "Oilseed farming"             "233230" "Manufacturing structures"              "Soybean Farming"                                    "111110" "236210" "Industrial Building Construction " 0  70668 27670 2582
"1111A0" "Oilseed farming"             "230301" "Nonresidential maintenance and repair" "Soybean Farming"                                    "111110" "236210" "Industrial Building Construction " 0 204428 99259 2582
"1111A0" "Oilseed farming"             "233230" "Manufacturing structures"              "Oilseed (except Soybean) Farming "                  "111120" "236210" "Industrial Building Construction " 0  70668 27670 2582
"1111A0" "Oilseed farming"             "230301" "Nonresidential maintenance and repair" "Oilseed (except Soybean) Farming "                  "111120" "236210" "Industrial Building Construction " 0 204428 99259 2582
"1111B0" "Grain farming"               "233230" "Manufacturing structures"              "Dry Pea and Bean Farming "                          "111130" "236210" "Industrial Building Construction " 0  70668 27670 2582
"1111B0" "Grain farming"               "230301" "Nonresidential maintenance and repair" "Dry Pea and Bean Farming "                          "111130" "236210" "Industrial Building Construction " 0 204428 99259 2582
"1111B0" "Grain farming"               "230301" "Nonresidential maintenance and repair" "Wheat Farming"                                      "111140" "236210" "Industrial Building Construction " 0 204428 99259 2582
"1111B0" "Grain farming"               "233230" "Manufacturing structures"              "Wheat Farming"                                      "111140" "236210" "Industrial Building Construction " 0  70668 27670 2582
"1111B0" "Grain farming"               "230301" "Nonresidential maintenance and repair" "Corn Farming "                                      "111150" "236210" "Industrial Building Construction " 0 204428 99259 2582
"1111B0" "Grain farming"               "233230" "Manufacturing structures"              "Corn Farming "                                      "111150" "236210" "Industrial Building Construction " 0  70668 27670 2582
"1111B0" "Grain farming"               "230301" "Nonresidential maintenance and repair" "Rice Farming"                                       "111160" "236210" "Industrial Building Construction " 0 204428 99259 2582
"1111B0" "Grain farming"               "233230" "Manufacturing structures"              "Rice Farming"                                       "111160" "236210" "Industrial Building Construction " 0  70668 27670 2582
"1111B0" "Grain farming"               "233230" "Manufacturing structures"              "Oilseed and Grain Combination Farming "             "111191" "236210" "Industrial Building Construction " 0  70668 27670 2582
"1111B0" "Grain farming"               "230301" "Nonresidential maintenance and repair" "Oilseed and Grain Combination Farming "             "111191" "236210" "Industrial Building Construction " 0 204428 99259 2582
"1111B0" "Grain farming"               "230301" "Nonresidential maintenance and repair" "All Other Grain Farming "                           "111199" "236210" "Industrial Building Construction " 0 204428 99259 2582
"1111B0" "Grain farming"               "233230" "Manufacturing structures"              "All Other Grain Farming "                           "111199" "236210" "Industrial Building Construction " 0  70668 27670 2582
"111200" "Vegetable and melon farming" "230301" "Nonresidential maintenance and repair" "Potato Farming "                                    "111211" "236210" "Industrial Building Construction " 0 204428 99259 2582
"111200" "Vegetable and melon farming" "233230" "Manufacturing structures"              "Potato Farming "                                    "111211" "236210" "Industrial Building Construction " 0  70668 27670 2582
"111200" "Vegetable and melon farming" "233230" "Manufacturing structures"              "Other Vegetable (except Potato) and Melon Farming " "111219" "236210" "Industrial Building Construction " 0  70668 27670 2582
"111200" "Vegetable and melon farming" "230301" "Nonresidential maintenance and repair" "Other Vegetable (except Potato) and Melon Farming " "111219" "236210" "Industrial Building Construction " 0 204428 99259 2582
end

As you can see in the dataset, there are many variables - code_user, code_supplier, naics_user, naics_supplier. This is a subset of my dataset. I have created a variable called 'sup' just to help me calculate the sum easily for the observations. In the first two lines, they are kind of repeated, but before collapsing the data, I want to sum these first two rows and then subsequent two rows (as there is a pattern) for total output variable in a new column. I hope this is clear. Any suggestions on this issue will help me expedite the research work. Thanks!

Kind Regards,
Preety

[Roman Mostazir]

If I understood correctly you want to add the first two rows of 'total_output_user' and 'total_interm_user' but the pattern is not consistent and breaks before line-7 or after line 6.

       t~t_user   t~m_user  
  1.      70668      27670  
  2.     204428      99259  
  3.      70668      27670  
  4.     204428      99259  
  5.      70668      27670  
  6.     204428      99259  
  7.     204428      99259

[Preety Bhogal]

The pattern stays when we control for naics_user and naics_supplier. There may be a way to sort the data and then sum the two rows where we have different code_user for the same pair of naics_supplier and naics_user.

[Roman Mostazir]

Okay got you, see if that's what you wanted:

byso naics_supplier: egen totout = total(total_output_user)
byso naics_supplier: egen totint = total(total_interm_user)