Need help for conditional sum

[Luisa Molinari]

Dear Stata experts,

I’m struggeling with Stata for my masterthesis and it would be great if you could help me.

My data consists of (ABS)bonds which belong to a specific deal/transaction (dealnr) and within the deal there is a certain order (order_)of the bonds. Now I’d like to find the subordinated volume (subvol_) for each bond within the deal. For example the subvol_ for the first bond should comprise the volume (vol) of the second and third bond within the deal; the subvol_ for the second bond is the volume of the third; and the subvol_ for the third bond is zero.

I tried:
sort dealnr order_
by dealnr: generate subvol_=sum(vol) if order_[n]>order_

…but I know there is something wrong.

The right column for subvol_ needs to be:
213
161,5
0
750
400
0


I'm grateful for any tips.

Thank you very much in advance.

[William Lisowski]

Welcome to Statalist, Luisa.

Something like the following might help.

sort dealnr order_
by dealnr: egen subvol_ = total(vol)
by dealnr: gen prevol = sum(vol)
replace subvol = subvol - prevol

I would have liked to test this on your sample data, but Stata doesn't read pictures.

For more thorough answers on future posts - and really, to increase the likelihood of an answer - you should review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. See especially sections 9-12 on how to best pose your question. It's particularly helpful to copy commands and output from your Stata Results window and paste them into your Statalist post using CODE delimiters, and to use the dataex command to provide sample data, as described in section 12 of the FAQ.

[Luisa Molinari]

Hey William,

thank you very much for your effort and advice. I tried it out and it worked perfectly fine!

[Nick Cox]

Luisa: Glad you have a solution.

Suggestion: "Need help for" could go on almost all questions here, so could just be cut in future. Tiny if not trivial point, but titles that are smarter get more attention.