Adding Values in Panel Data

[Friedrich Franz]

Hi, I have a problem in Stata and I hope that somebody can help me.

My panel dataset looks the following:

Date       ID     Input1 Input2
13880     4        1         .
13880     5        1         .
13880     6         .         .
13880     7        1         1
13880     8         .          .
13880     9         .          .
13880    10        .         1
13880    32        .          .
13880    33    8000    4500

I want to create a new variable that sums up the value of id==33 everytime the id has a 1.
For example:

Date     ID        Sum(Variable I would like to create)
13880   4            8000
13880   7           12500
13880   8             0

I tried a foreach loop, but I did not figure out how correctly reference to the id==33 data. I know it would be easier if I would run the id==33 as a seperate variable, but since I have multiple Input parameters it becomes a mess to have once a code variable that identifies whether the input is relevant for the id and the value of the input parameter in the second variable itself (I have about 100 Input variables).

I hope somebody can help me.

[Nick Cox]

If I understand you correctly you want something like this

bysort time : egen ref1 = total((id == 33) * input1)
by time : egen ref2 = total((id == 33) * input2)
gen new1 =  (input1 == 1) * ref1
gen new2 =  (input2 == 1) * ref2

If so, then generalisation is cheap:

forval j = 1/100 { 
         bysort time : egen ref`j' = total((id == 33) * input`j')
         gen new`j'=  (input`j' == 1) * ref`j'
}

Consider dropping each reference variable if it is no long-term use.

See also http://www.stata-journal.com/sjpdf.h...iclenum=dm0055 for a review of small techniques in this territory.

[Friedrich Franz]

Thank you, worked fine.

[Friedrich Franz]

Actually, I just discovered a rather big issue, which I do not know how to solve.

As mentioned earlier, your provided code works finde unless an Input parameter is completely missing, i.e. if Input 50 has missing for every date, the new variable should be zero, but instead it treats them as if Input50 would be full of 1 1 1 .....

For example:

 Date       ID     Input1 Input2       Input 50
13880     4        1         .             .
13880     5        1         .             .
13880     6         .         .            .  
13880     7        1         1             .  
13880     8         .          .           .
13880     9         .          .           .
13880    10        .         1             .
13880    32        .          .            .
13880    33    8000       4500           6000

The new variable should show zero at input50 for every id, but shows 6000. I guess it the fault of how "total" works, how can I fix this?

[Andrew Musau]

Could you reproduce this with an example? With your example data, Nick's code will generate all zeroes.

*Rename "Input50" "Input3"
input Date ID Input1 Input2 Input3
13880 4 1 . .
13880 5 1 . .       
13880 6 . . .          
13880 7 1 1             
13880 8 . . .        
13880 9 . . .
13880 10 . 1 .
13880 32 . . .
13880 33 8000 4500 6000
end


forval j = 1/3 {
bysort Date : egen ref`j' = total((ID == 33) * Input`j')
gen new`j'=  (Input`j' == 1) * ref`j'
}
l

. l

     +---------------------------------------------------------------------------------+
     |  Date   ID   Input1   Input2   Input3   ref1   new1   ref2   new2   ref3   new3 |
     |---------------------------------------------------------------------------------|
  1. | 13880    4        1        .        .   8000   8000   4500      0   6000      0 |
  2. | 13880    5        1        .        .   8000   8000   4500      0   6000      0 |
  3. | 13880    6        .        .        .   8000      0   4500      0   6000      0 |
  4. | 13880    8        .        .        .   8000      0   4500      0   6000      0 |
  5. | 13880    9        .        .        .   8000      0   4500      0   6000      0 |
     |---------------------------------------------------------------------------------|
  6. | 13880   10        .        1        .   8000      0   4500   4500   6000      0 |
  7. | 13880   32        .        .        .   8000      0   4500      0   6000      0 |
  8. | 13880   33     8000     4500     6000   8000      0   4500      0   6000      0 |
     +---------------------------------------------------------------------------------+

[Nick Cox]

As Andrew says, this shouldn't be the case. The code tests for whether inputs are 1 and is indifferent to what else they may be.

[Friedrich Franz]

Okay, your code works fine for the example I provided. However, I have the Input variable stored as double and instead of a "1" I have an x. When I extracted the example it got automatically converted to a 1 and I thought it would not matter, but it seems that it does. I fixed the problem by decoding the variable. Sorry, my mistake.

[Nick Cox]

OK; thanks for closure.