Variable not found when running loop even though it exists

[Beyza Yalcin]

Hello all,

This is my first time asking a question on here, so please excuse me if I am describing my issue in a wrong way. I use the version 14.2 and I am relatively new to Stata.
My problem is that when I run this code, I get the error message "SALESGR not found r(111)" even though the variable exists in my dataset as a float variable. If I try this with any other variable I get the same error message.
I work with an unbalanced panel datasset. The idea of this code is to calculate the mean values of SALESGR over the last 3 years. If one of the years is missing it should calculate the mean value of the 2 years available. If only one of the years is available then it should just take the value of the available year. Do you know what could be the problem? Many thanks in advance!

The code:

// Set the panel structure of the data
xtset ISIN_ID Year

// Create a new variable to store the mean value of SALESGR from _n to _n-2
generate SALESGR_mean = .

// Loop over all panel units
forvalues i = 1/`=_N' {
  // Check if SALESGR is missing in _n, _n-1, and _n-2 for the current panel unit
  if SALESGR[_n, `i'] == . & SALESGR[_n-1, `i'] == . & SALESGR[_n-2, `i'] == . {
    // If SALESGR is missing in all of these years, set SALESGR_mean to .
    replace SALESGR_mean = . in `i'
  } 
  else if SALESGR[_n, `i'] == . & SALESGR[_n-1, `i'] == . {
    // If SALESGR is missing in _n and _n-1, take the mean of _n-2
    replace SALESGR_mean = SALESGR[_n-2, `i'] in `i'
  } 
  else if SALESGR[_n, `i'] == . {
    // If SALESGR is missing in _n, take the mean of _n-1 and _n-2
    replace SALESGR_mean = (SALESGR[_n-1, `i'] + SALESGR[_n-2, `i']) / 2 in `i'
  } 
  else {
    // If SALESGR is present in _n, take the mean of _n, _n-1, and _n-2
    replace SALESGR_mean = (SALESGR[_n, `i'] + SALESGR[_n-1, `i'] + SALESGR[_n-2, `i']) / 3 in `i'
  }
}

[Nick Cox]

SALESGR with two subscripts implies a Stata matrix, not a variable, but the first subscript being observation number _n has no obvious meaning because matrices are independent of any dataset. But experiments indicate that _n as a matrix subscript will always be interpreted as 1, which is likely to prove puzzling.

Either way, I think Stata is objecting to the absence of a matrix and not any variable in your dataset.

I guess you mean to write SALESGR[`i']

However, your code is problematic even then: You set up panel data with xtset, but your following code ignores that panel structure.

It is rare that a loop over observations is needed in Stata and none is needed here..

You want a mean over the present and the last two values and have a preference to ignore missing values to the extent possible. That is exactly what tssmooth ma will do for you.

Here is a demonstration.

. webuse grunfeld, clear 

. 
. xtset company year 

Panel variable: company (strongly balanced)
 Time variable: year, 1935 to 1954
         Delta: 1 year

. 
. tssmooth ma wanted=invest, w(2 1 0) 
The smoother applied was
     by company : (1/3)*[x(t-2) + x(t-1) + 1*x(t)]; x(t)= invest

. 
. list year invest wanted if company == 1 

     +--------------------------+
     | year   invest     wanted |
     |--------------------------|
  1. | 1935    317.6      317.6 |
  2. | 1936    391.8      354.7 |
  3. | 1937    410.6   373.3333 |
  4. | 1938    257.7   353.3667 |
  5. | 1939    330.8   333.0333 |
     |--------------------------|
  6. | 1940    461.2      349.9 |
  7. | 1941      512   434.6667 |
  8. | 1942      448   473.7333 |
  9. | 1943    499.6   486.5333 |
 10. | 1944    547.5   498.3667 |
     |--------------------------|
 11. | 1945    561.2      536.1 |
 12. | 1946    688.1   598.9333 |
 13. | 1947    568.9   606.0667 |
 14. | 1948    529.2      595.4 |
 15. | 1949    555.1   551.0667 |
     |--------------------------|
 16. | 1950    642.9   575.7333 |
 17. | 1951    755.9      651.3 |
 18. | 1952    891.2   763.3334 |
 19. | 1953   1304.4   983.8334 |
 20. | 1954   1486.7   1227.433 |
     +--------------------------+

. 
. gen invest2 = cond(inlist(_n, 2, 3, 5, 7, 11, 13, 17, 19), ., invest) 
(8 missing values generated)

. 
. tssmooth ma wanted2=invest2, w(2 1 0)
The smoother applied was
     by company : (1/3)*[x(t-2) + x(t-1) + 1*x(t)]; x(t)= invest2

. 
. l year invest wanted invest2 wanted2 if company == 1 

     +-----------------------------------------------+
     | year   invest     wanted   invest2    wanted2 |
     |-----------------------------------------------|
  1. | 1935    317.6      317.6     317.6      317.6 |
  2. | 1936    391.8      354.7         .      317.6 |
  3. | 1937    410.6   373.3333         .      317.6 |
  4. | 1938    257.7   353.3667     257.7      257.7 |
  5. | 1939    330.8   333.0333         .      257.7 |
     |-----------------------------------------------|
  6. | 1940    461.2      349.9     461.2     359.45 |
  7. | 1941      512   434.6667         .      461.2 |
  8. | 1942      448   473.7333       448      454.6 |
  9. | 1943    499.6   486.5333     499.6      473.8 |
 10. | 1944    547.5   498.3667     547.5   498.3667 |
     |-----------------------------------------------|
 11. | 1945    561.2      536.1         .     523.55 |
 12. | 1946    688.1   598.9333     688.1      617.8 |
 13. | 1947    568.9   606.0667         .      688.1 |
 14. | 1948    529.2      595.4     529.2     608.65 |
 15. | 1949    555.1   551.0667     555.1     542.15 |
     |-----------------------------------------------|
 16. | 1950    642.9   575.7333     642.9   575.7333 |
 17. | 1951    755.9      651.3         .        599 |
 18. | 1952    891.2   763.3334     891.2     767.05 |
 19. | 1953   1304.4   983.8334         .      891.2 |
 20. | 1954   1486.7   1227.433    1486.7    1188.95 |
     +-----------------------------------------------+

Here's the code.

webuse grunfeld, clear 
xtset company year 
tssmooth ma wanted=invest, w(2 1 0) 

list year invest wanted if company == 1 

gen invest2 = cond(inlist(_n, 2, 3, 5, 7, 11, 13, 17, 19), ., invest) 
tssmooth ma wanted2=invest2, w(2 1 0)

l year invest wanted invest2 wanted2 if company == 1

Much of the demonstration is peppering the data with missing values and seeing what happens. You don't need to do that. You just need the tssmooth ma command, as you have already xtset your data.

[Beyza Yalcin]

Thank you Nick! Your code worked just fine and it's way simpler also! Appreciate it a lot