Stata code to conditionally sum values based on a group rank

Question

I'm trying to write a code for a fairly huge dataset (3m observations) which has been segregated into smaller groups (ID). For each observation (described in the table below), I want to create a cumulative sum of a variable "Value" for all observations ranked below me, subject to condition of the lower ranked observation equals mine.

[ Data[2]

I want to write this code without using loops, if there is a way to do so. Could someone help me?

Thank you!

UPDATE: I have pasted the equation for the output variable below.

UPDATE 2: The CSV format of the above table is:

ID,Rank,Condition,Value,Expected output,,
1,1,30,10,0,,
1,2,40,20,0,,
1,3,20,30,0,,
1,4,30,40,10,,
1,5,40,50,20,,
1,6,20,60,30,,
1,7,30,70,80,,
2,1,40,80,0,,
2,2,20,90,0,,
2,3,30,100,0,,
2,4,40,110,80,,
2,5,20,120,90,,
2,6,30,130,100,,
2,7,40,140,190,,
2,8,20,150,210,,
2,9,30,160,230,,

Equation

I can't follow the example at all. Guide us how through how (e.g.) 0, 0, 0, 10, 20, 30, 80 follow from your rules for id 1. — Nick Cox
Thank you Nick! I have updated the question with the equation. — SreeVathsan

Unknown Unknown · Accepted Answer · 2017-06-15T11:19:10

If I understand correctly, for each combination of ID and Condition, you want to calculate a running sum, ordered by Rank, of the variable Value, excluding the current observation. If that is indeed your goal, the following untested code might set you on the path to a solution

sort ID Condition Rank
// be sure there is a single observation for each combination
isid ID Condition Rank
// generate the running sum
by ID Condition (Rank): generate output = sum(Value)
// subtract out the current observation
replace output = output - Value
// return to the original order
sort ID Rank

As I said, this is untested, because my copy of Stata cannot read pictures of data. If your testing shows that it is imperfect and you cannot resolve the problem yourself, providing your sample data in a usable format will increase the likelihood someone will be able to help.

Added in edit: Corrected the isid command.

Stata code to conditionally sum values based on a group rank

1 Answers