How to get a weighted average value across time?

[Haofeng Zhou]

Hi all, I have a data now of asset of public firms across different period now. Suppoes that I have L firms, and T periods. I have three variables. The first one is firms ID, the second is period, and the third is the asset. The asset is like this. For each period t and firm l, there is an value of the firm's asset a_{t,l} (of course, there are many missing values, meaning this firm is not public at that time). What I want now is the weighted average of asset of all time, and the weight of each firm is the share of their asset in that period. That is, for each period t, I want to get \sum_l a_{t,l}^2/(\sum_l a_{t,l}). Does anyone know how to deal with it? I heard that it should be an easy job but I really know little about stata. I am sorry if my question is not clear enough. I don't know whether the forum allows us to upload picture. If yes please let me know, I will upload one.

[Clyde Schechter]

Based on some reasonable guesses of what your actual data set looks like, the following will do what you want:

by ID (period), sort: egen numerator = total(a^2)
by ID (period): egen denominator = total(a)
gen wanted = numerator/denominator

If it doesn't work for you, it will either because I have misunderstood your request (unlikely) or your data set is not as I imagine it to be. In the future, when asking for help with code, do not leave things to guesswork. If I have guessed wrong, we have both wasted our time. Show example data, and the helpful way to do that is with the -dataex- command. If you are running version 18, 17, 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.