Stata: sum of variable given other variable conditions

[Kabanga Shakankale]

I am very new to stata and help would be much appreciated. I'm trying to generate different 'total land' variables by caseid. The variable will then be used to create another variable showing the proportion of land a particular crop takes up. Could you help with generating a total land var? Thank you

* Example generated by -dataex-. For more info, type help dataex
clear
input str12 case_id str4 gardenid str3 plotid float LAND1 byte crop_code
"101011000014" "RG02" "R01"     1  3
"101011000023" "RG02" "R02"   .25 12
"101011000023" "RG02" "R01"   .75 38
"101011000023" "RG02" "R01"     1  2
"101011000040" "RG02" "R01"    .3 12
"101011000040" "RG02" "R01"    .4  1
"101011000071" "RG01" "R01"    .5 35
"101011000071" "RG01" "R02"    .3 34
"101011000071" "RG02" "R02"     1 12
"101011000071" "RG02" "R01"     1  3
"101011000095" "RG03" "R01"  .375 12
"101011000095" "RG03" "R01"  .125 38
"101011000095" "RG02" "R01"    .5  1
"101011000115" "RG01" "R01"    .5 35
"101011000115" "RG01" "R01"     1  1
"101011000126" "RG02" "R01"    .5  1
"101011000126" "RG02" "R01"    .5 12
"101011000126" "RG03" "R01"    .5  1
"101011000135" "RG02" "R01"   .75 38
"101011000135" "RG02" "R01"   1.5  4
"101011000183" "RG02" "R01"   .75 12
"101011000183" "RG02" "R01" 1.125  3
"101011000183" "RG02" "R01" 1.125 34
"101011000190" "RG02" "R01" 1.125  4
"101011000190" "RG02" "R01"  .375 12
"101011000190" "RG02" "R01"  .375 35
"101011000199" "RG01" "R01"    .8  1
"101011000229" "RG02" "R01"     1  3
end
label values crop_code ag_G_crop_roster__id
label def ag_G_crop_roster__id 1 "MAIZE LOCAL", modify
label def ag_G_crop_roster__id 2 "MAIZE COMPOSITE/OPV", modify
label def ag_G_crop_roster__id 3 "MAIZE HYBRID", modify
label def ag_G_crop_roster__id 4 "MAIZE HYBRID RECYCLED", modify
label def ag_G_crop_roster__id 12 "GROUNDNUT CG7", modify
label def ag_G_crop_roster__id 34 "BEANS", modify
label def ag_G_crop_roster__id 35 "SOYABEAN", modify
label def ag_G_crop_roster__id 38 "SUNFLOWER", modify

[Jared Greathouse]

See the help file for egen

[Carlo Lazzaro]

Kabanga:
you may want to try (after saving a copy of your original dataset on a different .dta file):

. encode case_id, g(num_case_id)

. collapse (sum) LAND1 , by( num_case_id )

.

[Nick Cox]

* Example generated by -dataex-. For more info, type help dataex
clear
input str12 case_id str4 gardenid str3 plotid float LAND1 byte crop_code
"101011000014" "RG02" "R01"     1  3
"101011000023" "RG02" "R02"   .25 12
"101011000023" "RG02" "R01"   .75 38
"101011000023" "RG02" "R01"     1  2
"101011000040" "RG02" "R01"    .3 12
"101011000040" "RG02" "R01"    .4  1
"101011000071" "RG01" "R01"    .5 35
"101011000071" "RG01" "R02"    .3 34
"101011000071" "RG02" "R02"     1 12
"101011000071" "RG02" "R01"     1  3
"101011000095" "RG03" "R01"  .375 12
"101011000095" "RG03" "R01"  .125 38
"101011000095" "RG02" "R01"    .5  1
"101011000115" "RG01" "R01"    .5 35
"101011000115" "RG01" "R01"     1  1
"101011000126" "RG02" "R01"    .5  1
"101011000126" "RG02" "R01"    .5 12
"101011000126" "RG03" "R01"    .5  1
"101011000135" "RG02" "R01"   .75 38
"101011000135" "RG02" "R01"   1.5  4
"101011000183" "RG02" "R01"   .75 12
"101011000183" "RG02" "R01" 1.125  3
"101011000183" "RG02" "R01" 1.125 34
"101011000190" "RG02" "R01" 1.125  4
"101011000190" "RG02" "R01"  .375 12
"101011000190" "RG02" "R01"  .375 35
"101011000199" "RG01" "R01"    .8  1
"101011000229" "RG02" "R01"     1  3
end

egen double total = total(LAND1), by(case_id) 
egen double crop = total(LAND1), by(case_id crop_code)

egen tag = tag(case_id crop_code)
gen wanted = crop / total if tag 

list case_id crop_code LAND1 total wanted if tag, sepby(case_id)

    +--------------------------------------------------------+
     |      case_id   crop_c~e   LAND1       total     wanted |
     |--------------------------------------------------------|
  1. | 101011000014          3       1           1          1 |
     |--------------------------------------------------------|
  2. | 101011000023         12     .25           2       .125 |
  3. | 101011000023         38     .75           2       .375 |
  4. | 101011000023          2       1           2         .5 |
     |--------------------------------------------------------|
  5. | 101011000040         12      .3   .70000002   .4285714 |
  6. | 101011000040          1      .4   .70000002   .5714285 |
     |--------------------------------------------------------|
  7. | 101011000071         35      .5         2.8   .1785714 |
  8. | 101011000071         34      .3         2.8   .1071429 |
  9. | 101011000071         12       1         2.8   .3571429 |
 10. | 101011000071          3       1         2.8   .3571429 |
     |--------------------------------------------------------|
 11. | 101011000095         12    .375           1       .375 |
 12. | 101011000095         38    .125           1       .125 |
 13. | 101011000095          1      .5           1         .5 |
     |--------------------------------------------------------|
 14. | 101011000115         35      .5         1.5   .3333333 |
 15. | 101011000115          1       1         1.5   .6666667 |
     |--------------------------------------------------------|
 16. | 101011000126          1      .5         1.5   .6666667 |
 17. | 101011000126         12      .5         1.5   .3333333 |
     |--------------------------------------------------------|
 19. | 101011000135         38     .75        2.25   .3333333 |
 20. | 101011000135          4     1.5        2.25   .6666667 |
     |--------------------------------------------------------|
 21. | 101011000183         12     .75           3        .25 |
 22. | 101011000183          3   1.125           3       .375 |
 23. | 101011000183         34   1.125           3       .375 |
     |--------------------------------------------------------|
 24. | 101011000190          4   1.125       1.875         .6 |
 25. | 101011000190         12    .375       1.875         .2 |
 26. | 101011000190         35    .375       1.875         .2 |
     |--------------------------------------------------------|
 27. | 101011000199          1      .8   .80000001          1 |
     |--------------------------------------------------------|
 28. | 101011000229          3       1           1          1 |
     +--------------------------------------------------------+

.

[Kabanga Shakankale]

Thank you so much for your answers. I have been well able to create the total land and the proportions for each crop.