How to sum values that occur on the same date that belong to the ID?

[Austin Wajs]

Hello,

The data I am working with on Stata is financial stock data between the years of 2012-2017. I have created an ID variable for the position holder (there are around 300 unique position holders). Each position holder has holdings in more than one stock. Due to this, there are instances where the position holders trade multiple stocks in one day. In order to declare panel data, I created a unique ID for each position holder + stock holding. Therefore each panel is a unique position holders' transaction history in one stock between 2012-2017.

I have generated a PaperGain variable, which generates the value 1 if the stock is either sold for profit or could be sold for profit on that particular day (either traded or it could have been traded for gain). I would like to sum this PaperGain value for each date a stock is transacted for each position holder in every stock they own. This is because there may be multiple PaperGain's on a certain day that the position holder owns, and so I would like to sum this altogether. Is there a way to do this?

Regards

[Carlo Lazzaro]

Austin:
welcome to this forum.
As per FAQ, please note that and example/excerpt of your dataset shared via -dataex- (see -help dataex-) can help interested listers in helping you out more than many lines aimed at describing what's the matter with your data. Thanks.

[Austin Wajs]

Hello,

I am using Stata 14 and used dataex for the following:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(PHIDISIN bdate) double ContShortPosition float(cfPHID PaperGain)
2 169  .5 1 1
2 170 .49 1 1
2 171 .49 1 0
2 172 .49 1 0
2 173 .49 1 0
2 174 .49 1 0
2 175 .49 1 0
2 176 .49 1 0
2 177 .49 1 0
2 178 .49 1 0
2 179 .49 1 0
2 180 .49 1 0
2 181 .49 1 0
2 182 .49 1 0
2 183 .49 1 0
2 184 .49 1 0
2 185 .49 1 0
2 186 .49 1 0
2 187 .49 1 0
2 188 .49 1 0
end
format %tbftse: bdate

Originally I have:
xtset PHIDISIN bdate, format(%tbftse)

where, 'PHIDISIN' is the unique ID variable for Position Holder + ISIN (which is the Internation Securities Identification Number). 'cfPHID' is the unique ID variable for Position Holder. 'bdate' is the business date variable in the dataset. 'ContShortPosition' is the current position held in the stock by the Position Holder. 'PaperGain' is a dummy variable that takes value 1 if a gain could be made on that specific date by trading that specific stock.

Interpreting the example, Company with cfPHID=1 that has a position in a specific stock (i.e. PHIDISIN=2) could have made a paper gain on bdate=169 or bdate=170, but no other dates (in this example).

However in my dataset (with around 1.8 million observations) all companies have multiple stock holdings (i.e. each cfPHID has multiple PHIDISINs within them). Therefore there are cases where on the same bdate (for example on bdate=169) there are paper gains for other stocks that the current company holds. I would like to sum all of the paper gain values for each bdate, by each Position Holder. For example if there are two other paper gains that could be made on bdate=169 by cfPHID=1, sum of paper gain will be equal to 3.

Regards,
Austin