Calculate total/collapse part of the values of one variable based on values of another category variable

[Craig Yang]

Dear All,

In my dataset I have a categorical variable with different sub-categories:

A1 A2 A3 A4 B1 B2 C1 C2 C3

I have several other variables (var1 var2 var3...) with numeric values corresponding to each of the category. I'm trying to calculate the total of the values for each sub-category and collapse the categorical variable into general categories like:

A B C

where the values corresponding to A is the sum of A1, A2, A3 A4, the values corresponding to B is the sum of B1, B2, ...

Haven't figure out a way to do it yet...any suggestions?

Any help is appreciated!

Many thanks,
Craig

[Clyde Schechter]

I'm not sure I understand what you have and what you want to get. It would have been much more helpful to show an example of the data you have (using -dataex-) and also to show what the result you are trying to get is. But it sounds to me like:

egen A = rowtotal(A1 A2 A3 A4)
egen B = rowtotal(B1 B2)
egen C = rowtotal(C1 C2 C3)

If that's not it, then do post back with the additional information I requested

If you are running version 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Craig Yang]

Hi Clyde, thank you very much! Your suggestions are very helpful. And just tried dataex, it's very handy. Will use it next time!