Generate a variable by year

[FatimaZohra SABRANE]

Hello,

I have a quarterly panel data and I want to generate a variable 'plants' that is the sum of plant(1)+...+plant(n) by year and not by quarter, in a way to have the same value for 'plants' in each quarter of the same year.
I tried the following code but it gives values by quarter.

gen plants= plant1+plant2+plant3+plant4+plant5+plant6+plant7+plant8+plant9+plant10+plant11+plant12+plant13

Hope you can help with that.

[Nick Cox]

Here's a guess in the absence of a data example:

egen wanted1 = rowtotal(plant*)
egen wanted2 = total(wanted1), by(year)

[FatimaZohra SABRANE]

I am afraid it did not work.

list qd id year plant* wanted* in 1/10

     +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     |  qdate         id       year       p~1   p~2   p~3     p~4   p~5    p~6    p~7   p~8    p~9     p~10   p~11   p~12   p~13 plants wanted1 wanted2   wanted3 |
     |-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
  1. | 2016q1   A106   2016        1        0        0        0        0        0        0        1        0         0         0         0         0        2         2          692                     3 |
  2. | 2016q2   A106   2016        1        0        0        0        0        0        0        0        0         0         0         0         0        1         1          692                     3 |
  3. | 2016q3   A106   2016        0        0        0        0        0        0        0        0        0         0         0         0         0        0         0          692                     3 |
  4. | 2016q4   A106   2016        0        0        0        0        0        0        0        0        0         0         0         0         0        0         0          692                     3 |
  5. | 2015q1   a001   2015        0        1        0        0        0        0        0        0        0         0         0         0         0        1         2        2768                     8 |
     |-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
  6. | 2015q2   a001   2015        1        0        0        0        0        0        0        0        0         0         0         0         0        1         2        2768                     8 |
  7. | 2015q3   a001   2015        1        0        0        0        0        0        0        0        0         0         0         0         0        1         2        2768                     8 |
  8. | 2015q4   a001   2015        0        0        1        0        0        0        0        0        0         0         0         0         0        1         2        2768                     8 |
  9. | 2016q1   a001   2016        0        0        1        0        0        0        0        0        0         0         0         0         0        1         1          692                     3 |
 10. | 2016q2   a001   2016       0        0        0        0        0        0        0        0        0         0         0         0         0        0         0          692                     3 |
     +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

I got wanted3 by specifying by year and id:

 
 egen wanted3 = total(wanted1), by(year id)

The data example is:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(plant1 plant2 plant3 plant4 plant5 plant6 plant7 plant8 plant9 plant10 plant11 plant12 plant13) float plants int year float id
1 0 0 0 0 0 0 1 0 0 0 0 0 2 2016 1
1 0 0 0 0 0 0 0 0 0 0 0 0 1 2016 1
0 0 0 0 0 0 0 0 0 0 0 0 0 0 2016 1
0 0 0 0 0 0 0 0 0 0 0 0 0 0 2016 1
0 1 0 0 0 0 0 0 0 0 0 0 0 1 2015 2
1 0 0 0 0 0 0 0 0 0 0 0 0 1 2015 2
1 0 0 0 0 0 0 0 0 0 0 0 0 1 2015 2
0 0 1 0 0 0 0 0 0 0 0 0 0 1 2015 2
0 0 1 0 0 0 0 0 0 0 0 0 0 1 2016 2
0 0 0 0 0 0 0 0 0 0 0 0 0 0 2016 2
end
label values id id
label def id 1 "A106", modify
label def id 2 "a001", modify

So from the table above, the values of 'plants' for the first id in 2016 should be equal to 2 as I am interested to see if farmers have monoculture or multi-cropping in each year.

[Nick Cox]

Indeed. If you want totals over identifier and year you need different code. You asked for totals over year.

[FatimaZohra SABRANE]

May I know this code I still didn't solve it.

[Nick Cox]

You cited it yourself as the code for wanted3.

[FatimaZohra SABRANE]

So from the table above, the values of ('plants'=>wanted3) for the first id in 2016 should be equal to 2 as I am interested to see if farmers have monoculture or multi-cropping in each year.

wanted3 shows that the number of crops the first household grow in 2016 is 3 not 2. So the code is not correct.
the thing is, I want that each crop should only be counted once for each id and year.

[Nick Cox]

You may need to create a new variable using say egen.

https://www.stata.com/support/faqs/d...ble-recording/ may help.

[FatimaZohra SABRANE]

Thanks Nick for the assistance.
It worked with the following code:

egen bisplant1=max(plant1), by (year id)
egen bisplant2=max(plant2), by (year id)
egen bisplant3=max(plant3), by (year id)
egen bisplant4=max(plant4), by (year id)
egen bisplant5=max(plant5), by (year id)
egen bisplant6=max(plant6), by (year id)
egen bisplant7=max(plant7), by (year id)
egen bisplant8=max(plant8), by (year id)
egen bisplant9=max(plant9), by (year id)
egen bisplant10=max(plant10), by (year id)
egen bisplant11=max(plant11), by (year id)
egen bisplant12=max(plant12), by (year id)
egen bisplant13=max(plant13), by (year id)
egen bisplants= rowtotal(bisplant*)
gen byte monoculture= inlist(1, bisplants)

Hope it would be useful for others in the future!

[Nick Cox]

Thanks for the closure. Now that there is a solution we can tweak it:

forval j = 1/13 {     
    egen bisplant`j' = max(plant`j'), by (year id)  
}

gives you all the "any" indicators in a loop.

[FatimaZohra SABRANE]

That's great to know!

Thanks Nick