Calculating sum of data for certain firm at point in time (panel data)

[Ralph Klijn]

Hello all,

First of all, I am quiet inexperienced in Stata, to beforehand apologize for the probably vague description and my appreciation for taking the effort to read this.

So this is my situation: I have a panel data set with firms, years and the firms' owners. For every year and every firm, all the holdings of the owners are given separately. I would like to calculate the sum of these holdings for every firm at every point in time.

I have included a picture showing what my dataset looks like. As you can see for every point in time (fdate) and firm (cusip), I have holdings by multiple parties, as shown under 'shares'. I want to create a new variable per firm per point in time with the sum of these holdings.

What is the best way to do this?

Thank you and best regards,
Ralph Klein

[Nick Cox]

https://www.statalist.org/forums/help#stata explains why screenshots are fairly useless. Please read and act on it, and then you'll get good specific answers from somebody.

[Ralph Klijn]

My apologies. As I am not using my own computer, I cannot download Stata 15.1 and use the command dataex.

However, below is an excerpt of what my data looks like. Is this of any use?

fdate	cusip	shares
20060331 20051231 20070630 20060331 20070930 20061231 20080331 20071231 20071231 20051231 20051231 20070331 20070331 20070331 20060630 20050331 20051231 20060331 20070930 20060930 20071231 20071231 20071231 20070331	00080S10 00080S10 00080S10 00080S10 00080S10 00080S10 00080S10 00080S10 00080S10 00080S10 00080S10 00080S10 00080S10 00080S10 00080S10 00080S10 00080S10 00080S10 00080S10 00080S10 00080S10 00080S10 00080S10 00080S10	2087582 10099 674745 234623 45100 200 61034 400238 10745 73300 50418 170674 325000 69775 12800 1071580 191900 59800 5432864 57100 2315043 53176 1660020 81400

[Nick Cox]

You don't need to have access to 15.1. You can download dataex from SSC. This is explained at the point cited.

Also, running two threads at once can be awkward. See answer at https://www.statalist.org/forums/for...r-observations

You may want either

egen total = total(shares), by(cusip fdate)

or

collapse (sum) shares, by(cusip fdate)