Calculating weights using by

[Pim Mante]

Hi Statalists,

Currently I'm trying to calculate the weights in subsets of my data using

by cusip date exdate cp: gen oiw = open_interest/sum(open_interest)

Looking at the output I can see that something is wrong in my understanding of what this code would do as it assigns oiw (open interest weight) of 1 to the first observation in the subset and only proceeds to use the sum of open interest in the following observations (adding to the total sum value as it proceeds down the rows in the subset). I want my code to immediately divide by the open interest for that row in the subset by the total for that subset. Any advice on how to amend the code in order for it to take into account the sum of all observations for open interest in the subset?
PS. I am using Stata 15.1.

[Nick Cox]

The help explains that sum() is not a function to calculate group totals, except in so far as they will be shown in the last observation of any group.

sum(x)
Description: the running sum of x, treating missing values as zero

For example, following the command generate y=sum(x), the jth observation on y contains the sum of the first through jth observations on x. See [D] egen for an
alternative sum function, total(), that produces a constant equal to the overall sum.
Domain: all real numbers or missing
Range: -8e+307 to 8e+307 (excluding missing)

  
 by cusip date exdate cp: egen double oiw = pc(open_interest), prop

is a one-line alternative.

A way to fix your approach is

by cusip date exdate cp: gen double oiw = sum(open_interest)
by cusip date exdate cp: replace oiw = open_interest / oiw[_N]

[Pim Mante]

Thank you for your guidance once again Nick.
Using the pc function with the prop option indeed provides the values I was looking for.
I had indeed noticed that the sum was only giving the correct weight for the last observation in the group, as explained in the help section.