Nested forval loop and overwriting a variable

[Burcu Deniz]

I am trying to replace values of a variable in a specific cell using nested loops and lagged variables. I want to refer to the first macro in the second loop. However, I keep receiving an error message. Stata says “_n- invalid name”.

I am trying to use the current values of the same variable (e.g variable “total”), lagged values of other variables (e.g variable “amount”) to overwrite the variable “total”. Is it possible to do this in Stata?

forval i = 2/17 {

forval j = 2/`i' {

replace total= total+amount[_n-``j'+1']*(endrate-begrate[_n-``j'+1']) if _n=`i' & _n>1

}
}

[Nick Cox]

I'd back up and explain the overarching problem because there's likely to be a simpler way to proceed. Further, even when made legal I doubt that this code does what you want.

But you have at least two immediate problems.

``j'+1'

is either illegal or not what you want.

`=`j' + 1'

is possibly what you mean.

if _n=`i' & _n>1

Stata can't make sense of this because tests for equality must be phrased in terms of == not =. The error message is confusing but the reason is that Stata is confused about what you want.

That could be rewritten

if _n==`i' & _n>1

and now it's legal. But it would be simpler to say

in `i'

and you can omit the qualifier

if _n > 1

as you are looping over observations 2 to 17 and that qualifier is redundant, although not harmful.

Otherwise put, at most this code will make changes in observations 2 to 17 of your entire dataset. If you have a larger dataset, and/or panel data. that is unlikely to be what you want.

As said, I would back up: please show us a data example with variables amount begrate endrate and explain your rule for calculating total.

If you have panel data, that needs to be explained too.

[Burcu Deniz]

Thank you Nick for your reply.
Actually, I have panel data. I was just trying to see if the loop works as I wanted for one small part of my data. I attached a sample dataset.

First, I generate total as below:

• by panelid Date, sort: gen total=amount*(endrate-begrate)

Then, I want to replace total (by panelid date) with the following rule:

• 1st date: total=amount*(endrate-begrate)
• 2nd date: total=total+amount[_n-1]*(endrate-begrate[_n-1])
• 3rd date: total=total+amount[_n-1]*(endrate-begrate[_n-1])+amount[_n-2]*(endrate-begrate[_n-2])

And it goes on like this for all dates within each id.

[Nick Cox]

Sounds like

bysort panelid (Date): gen double total = sum(amount * (endrate-begrate))

as sum() yields the cumulative or running sum.

[Burcu Deniz]

But I need to use the value of endrate at time of t and amount and begrate at time t-1 or t-2. That is why, sum is not working for me. Below is the rule I want to use with time indices.

• t=1: total_t=amount_t*(endrate_t-begrate_t)
• t=2: total_t=amount_t*(endrate_t-begrate_t)+amount_t-1*(endrate_t-begrate_t-1)
• t=3: total_t=amount_t*(endrate_t-begrate_t)+amount_t-1*(endrate-begrate_t-1)+amount_t-2*(endrate_t-begrate_t-2)

So for t=3, the rule is total₃=amount₃*(endrate₃-begrate₃)+amount₂*(endrate₃-begrate₂)+amount₁*(endrate₃-begrate₁)

[Nick Cox]

Here is my next guess.

bysort panelid (Date) : gen t = Date - Date[1] + 1 
su t, meanonly 
local T = r(max) - 1 
tsset panelid t 

gen double total = amount * (endrate-begrate))  

forval lag = 1/`T' { 
    replace total = total + L`lag'.amount * (endrate - L`lag'.begrate) if t > `lag'
}

[Burcu Deniz]

Thank you for your help!
The code only calculates total when t=1 ang gives missing value in other rows.

[Nick Cox]

gen double total = amount * (endrate-begrate)

[Nick Cox]

The code only calculates total when t=1 ang gives missing value in other rows.

Oh well. Sorry about that. Gaps in your data could be a problem. At least you have some suggestions you can take forward.

[Nick Cox]

If you're happy to ignore gaps, then

bysort panelid (Date) : gen t = _n

should help.

[Mohamed Mahmoud]

Dear Nick ,


Sorry for revisiting this again , I have a question similar

This is my data looks like

* Example generated by -dataex-. For more info, type help dataex
clear
input double year str88 Name long(B A)
2016 "AGREE, JOEL"               0 16200
2017 "AGREE, JOEL"               0  3200
2018 "AGREE, JOEL"               0  5400
2019 "AGREE, JOEL"               0  5600
2011 "AKERSON, DANIEL"        5000  5000
2012 "AKERSON, DANIEL"        1000     0
2012 "AKINS, NICHOLAS"         500  1200
2013 "AKINS, NICHOLAS"        3300  1200
2014 "AKINS, NICHOLAS"        1000  1200
2016 "AKINS, NICHOLAS"        2700  5400
2017 "AKINS, NICHOLAS"           0  2700
2018 "AKINS, NICHOLAS"           0  2700
2019 "AKINS, NICHOLAS"           0  1250
2020 "AKINS, NICHOLAS"           0  3100
2011 "ALBRECHT, CHRISTOPHER"  6000     0
2013 "ALBRECHT, CHRISTOPHER" 37400     0
end

What I need can be summarized in 3 steps

1) by name, get the total amount of spending on "A" & the total amount of spending on group "B" & Total of Both.
2) then by name, get the difference between the previous total mounts ( A-B)
3) get the ratio by dividing the difference between the total two spending by the total amount of spending.


for example AKERSON, DANIEL

1)Total "A" spending = 6000 ; Total "B" spending = 5000 ; Total of both = 11,000
2) Diff. = 5,000 - 6000 = -1,000
3) Ratio = -1000/Total of A & B 11,000 = -0.09

I started to construct the variable by using this command but I got incorrect results

 bysort name year: egen sum_spending = sum(A+ B)

Could you please provide any suggestions/or steps to solve this problem without using command -collapse- ?