Using formulas in Stata

[Mangji Zo]

Hi, I am relatively new to using Stata. I have a question as to how to convert quantities of crops data to kcal for each crop. For instance, For banana/banana food (crop code 741) is 48kg and I want to convert to 100 kcal per 100g and similarly I want to convert cassava to 350kcal per 100g. Any advice is would be really helpful! Thank you!

* Example generated by -dataex-. To install: ssc install dataex
clear
input str14 HHID str20 Crop_name int Crop_code float type_quan_crop
"1021000108"   "Banana food"    741  48
"1021000108"   "Cassava"        630   3
"1021000113"   "Beans"          210   3
"1021000113"   "Dodo"           460   2
"1021000113"   "Dodo"           460  10
"1021000113"   "Maize"          130   1
"1021000408"   "Beans"          210   2
"1021000408"   "Maize"          130   1
"1021000710"   "Beans"          210 3.5
"1021000710"   "Maize"          130  60
"1021000807"   "Banana food"    741 100
"1021000807"   "Banana food"    741  30
"1021000807"   "Banana food"    741   0
"1021000807"   "Cassava"        630   0
"102100080803" "Banana food"    741  20
"102100080803" "Beans"          210   1
"102100080803" "Maize"          130   0
"102100080803" "Groundnuts"     310   2
"102100080803" "Maize"          130   0
"102100080803" "Cassava"        630   2
"102100080803" "Maize"          130 1.5
"102100080803" "Sweet potatoes" 620   1
"102100110201" "Beans"          210   1
"102100110201" "Groundnuts"     310   3
"102100110201" "Groundnuts"     310   2
"102100110201" "Beans"          210   1
"1021001109"   "Sweet potatoes" 620  32
"1021001109"   "Banana food"    741  12
"1021001109"   "Cassava"        630   2
"1021001109"   "Maize"          130  .5
"1021001109"   "Yam"            640   4
"1021001109"   "Sugarcane"      510 120
"1021001304"   "Banana food"    741   4
"1021001304"   "Beans"          210   4
"1021001304"   "Beans"          210  10
"1021001304"   "Cassava"        630   5
"1021001304"   "Maize"          130   1
"1021002610"   "Banana food"    741  10
"1021002610"   "Sweet potatoes" 620   5
"1021002610"   "Yam"            640   5
"1021002610"   "Groundnuts"     310   1
"1021002610"   "Maize"          130   1
"1021002610"   "Sweet potatoes" 620   2
"1021002611"   "Maize"          130   1
"1021002611"   "Maize"          130   0
"1021002810"   "Beans"          210  .5
"1021002810"   "Maize"          130   2
"1033000301"   "Banana food"    741  60
"1033000301"   "Maize"          130  15
"1033000302"   "Beans"          210   2
"1033000302"   "Banana food"    741  10
"1033000302"   "Beans"          210   1
"1033000303"   "Maize"          130   1
"1033000303"   "Banana food"    741  35
"1033000304"   "Banana food"    741  40
"1033000304"   "Banana beer"    742  30
"1033000304"   "Beans"          210   1
"1033000304"   "Maize"          130  20
"103300030403" "Banana food"    741   5
"103300030403" "Banana food"    741   6
"103300030403" "Beans"          210   1
"103300030403" "Maize"          130  .5
"1033000307"   "Banana beer"    742  30
"1033000307"   "Banana beer"    742  10
"1033000307"   "Banana food"    741 130
"1033000308"   "Maize"          130   0
"1033000308"   "Beans"          210   4
"1033000308"   "Irish potatoes" 610   2
"1033000308"   "Maize"          130   0
"1033000309"   "Banana food"    741   7
"1033000309"   "Banana food"    741  30
"1033000310"   "Banana food"    741  20
"1033000504"   "Banana food"    741  48
"1033000504"   "Beans"          210   1
"1033000504"   "Maize"          130   1
"1033000504"   "Beans"          210   1
"1033000505"   "Banana food"    741  26
"1033000505"   "Beans"          210   5
"1033000506"   "Beans"          210   1
"1033000506"   "Maize"          130   2
"1033000506"   "Groundnuts"     310   3
"1033000506"   "Irish potatoes" 610   3
"1033000507"   "Beans"          210   2
"1033000507"   "Cassava"        630   6
"1033000509"   "Beans"          210   3
"1033000510"   "Sweet potatoes" 620   2
"1033000510"   "Beans"          210   4
"1033000510"   "Maize"          130   1
"1033000510"   "Beans"          210   4
"1033000510"   "Maize"          130   6
"1033000511"   "Maize"          130   0
"1033000511"   "Beans"          210   1
"1033000511"   "Cassava"        630   5
"1033000511"   "Maize"          130   0
"1033000511"   "Beans"          210  .5
"1033000511"   "Maize"          130   2
"103300051102" "Banana beer"    742  18
"103300051102" "Banana food"    741  30
"1041000210"   "Maize"          130  13
"1041000210"   "Sweet potatoes" 620   1
end

------------------ copy up to and including the previous line ------------------

Listed 100 out of 9560 observations

[Bruce Weaver]

Hello Mangji. First, thanks for using -dataex- to provide sample data. Second, I don't see anything in your data that can be converted to kcal per 100g by means of mathematical equation. It seems to me that you need another file to be used as a look-up table. It must contain two variables, Crop_code and another variable giving the kcal per 100g. Presumably, you know those vales, or can look them up somewhere. Here's an example of what I mean. I've only included crop codes 630 and 741 in my lookup table, because those are the only two for which you reported the kcal per 100 g. ;-)

* Create a look-up table with the kcal/100g values by Crop_code
clear *
input int Crop_code kcal100g
741 100
630 350
end
sort Crop_code
save "C:/temp/kcal_lookup.dta", replace

* Example generated by -dataex-. To install: ssc install dataex
clear
input str14 HHID str20 Crop_name int Crop_code float type_quan_crop
"1021000108"   "Banana food"    741  48
"1021000108"   "Cassava"        630   3
"1021000113"   "Beans"          210   3
"1021000113"   "Dodo"           460   2
"1021000113"   "Dodo"           460  10
"1021000113"   "Maize"          130   1
"1021000408"   "Beans"          210   2
"1021000408"   "Maize"          130   1
"1021000710"   "Beans"          210 3.5
"1021000710"   "Maize"          130  60
"1021000807"   "Banana food"    741 100
"1021000807"   "Banana food"    741  30
"1021000807"   "Banana food"    741   0
"1021000807"   "Cassava"        630   0
"102100080803" "Banana food"    741  20
"102100080803" "Beans"          210   1
"102100080803" "Maize"          130   0
"102100080803" "Groundnuts"     310   2
"102100080803" "Maize"          130   0
"102100080803" "Cassava"        630   2
"102100080803" "Maize"          130 1.5
"102100080803" "Sweet potatoes" 620   1
"102100110201" "Beans"          210   1
"102100110201" "Groundnuts"     310   3
"102100110201" "Groundnuts"     310   2
"102100110201" "Beans"          210   1
"1021001109"   "Sweet potatoes" 620  32
"1021001109"   "Banana food"    741  12
"1021001109"   "Cassava"        630   2
"1021001109"   "Maize"          130  .5
"1021001109"   "Yam"            640   4
"1021001109"   "Sugarcane"      510 120
"1021001304"   "Banana food"    741   4
"1021001304"   "Beans"          210   4
"1021001304"   "Beans"          210  10
"1021001304"   "Cassava"        630   5
"1021001304"   "Maize"          130   1
"1021002610"   "Banana food"    741  10
"1021002610"   "Sweet potatoes" 620   5
"1021002610"   "Yam"            640   5
"1021002610"   "Groundnuts"     310   1
"1021002610"   "Maize"          130   1
"1021002610"   "Sweet potatoes" 620   2
"1021002611"   "Maize"          130   1
"1021002611"   "Maize"          130   0
"1021002810"   "Beans"          210  .5
"1021002810"   "Maize"          130   2
"1033000301"   "Banana food"    741  60
"1033000301"   "Maize"          130  15
"1033000302"   "Beans"          210   2
"1033000302"   "Banana food"    741  10
"1033000302"   "Beans"          210   1
"1033000303"   "Maize"          130   1
"1033000303"   "Banana food"    741  35
"1033000304"   "Banana food"    741  40
"1033000304"   "Banana beer"    742  30
"1033000304"   "Beans"          210   1
"1033000304"   "Maize"          130  20
"103300030403" "Banana food"    741   5
"103300030403" "Banana food"    741   6
"103300030403" "Beans"          210   1
"103300030403" "Maize"          130  .5
"1033000307"   "Banana beer"    742  30
"1033000307"   "Banana beer"    742  10
"1033000307"   "Banana food"    741 130
"1033000308"   "Maize"          130   0
"1033000308"   "Beans"          210   4
"1033000308"   "Irish potatoes" 610   2
"1033000308"   "Maize"          130   0
"1033000309"   "Banana food"    741   7
"1033000309"   "Banana food"    741  30
"1033000310"   "Banana food"    741  20
"1033000504"   "Banana food"    741  48
"1033000504"   "Beans"          210   1
"1033000504"   "Maize"          130   1
"1033000504"   "Beans"          210   1
"1033000505"   "Banana food"    741  26
"1033000505"   "Beans"          210   5
"1033000506"   "Beans"          210   1
"1033000506"   "Maize"          130   2
"1033000506"   "Groundnuts"     310   3
"1033000506"   "Irish potatoes" 610   3
"1033000507"   "Beans"          210   2
"1033000507"   "Cassava"        630   6
"1033000509"   "Beans"          210   3
"1033000510"   "Sweet potatoes" 620   2
"1033000510"   "Beans"          210   4
"1033000510"   "Maize"          130   1
"1033000510"   "Beans"          210   4
"1033000510"   "Maize"          130   6
"1033000511"   "Maize"          130   0
"1033000511"   "Beans"          210   1
"1033000511"   "Cassava"        630   5
"1033000511"   "Maize"          130   0
"1033000511"   "Beans"          210  .5
"1033000511"   "Maize"          130   2
"103300051102" "Banana beer"    742  18
"103300051102" "Banana food"    741  30
"1041000210"   "Maize"          130  13
"1041000210"   "Sweet potatoes" 620   1
end

sort Crop_code
merge m:1 Crop_code using "C:/temp/kcal_lookup.dta"

HTH.

[Mangji Zo]

Hi Bruce,

Thank you for the helpful feedback! Unfortunately, I don't have a mathematical formula to convert the kg to calories. But I want to convert all the wheat using its crop code 111 to convert to kcal. The quantity of wheat is 20kg and I want to convert that to grams first by multiplying by 1000. Then I want to multiply 3.71kcal/gram for the conversion.

Is it possible to do this for each type of crop? Wheat, Rice, etc

I don't have much experience creating look up tables, I tried to create one using your example but Stata says that Crop_code already exists.
Thanks for any advice!

* Example generated by -dataex-. To install: ssc install dataex
clear
input str20 Crop_name int Crop_code float type_quan_crop
"Wheat" 111   20
"Wheat" 111   25
"Wheat" 111    0
"Rice"  120    0
"Rice"  120    3
"Rice"  120    4
"Rice"  120    2
"Rice"  120    0
"Rice"  120    1
"Rice"  120    2
"Rice"  120   50
"Rice"  120    2
"Rice"  120   14
"Rice"  120    4
"Rice"  120    1
"Rice"  120    1
"Rice"  120    0
"Rice"  120    5
"Rice"  120  300
"Rice"  120    0
"Rice"  120    1
"Rice"  120  8.5
"Rice"  120  300
"Rice"  120   30
"Rice"  120    2
"Rice"  120    1
"Rice"  120    7
"Rice"  120    2
"Rice"  120    1
"Rice"  120    2
"Rice"  120  .95
"Rice"  120  140
"Rice"  120   .5
"Rice"  120  520
"Rice"  120  320
"Rice"  120   .5
"Rice"  120  200
"Rice"  120  100
"Rice"  120   90
"Rice"  120  100
"Rice"  120  100
"Rice"  120    2
"Rice"  120   .1
"Rice"  120    0
"Rice"  120  280
"Rice"  120    2
"Rice"  120    2
"Rice"  120    0
"Rice"  120    8
"Rice"  120   11
"Rice"  120   50
"Rice"  120    0
"Rice"  120  100
"Rice"  120  100
"Rice"  120    2
"Rice"  120   13
"Rice"  120  100
"Rice"  120 3.05
"Rice"  120   50
"Rice"  120    6
"Rice"  120   70
"Rice"  120   10
"Rice"  120    7
"Rice"  120    1
"Maize" 130  200
"Maize" 130  150
"Maize" 130  1.5
"Maize" 130    1
"Maize" 130  300
"Maize" 130    3
"Maize" 130    5
"Maize" 130   10
"Maize" 130    1
"Maize" 130  200
"Maize" 130    1
"Maize" 130  2.2
"Maize" 130    7
"Maize" 130  180
"Maize" 130   30
"Maize" 130   .5
"Maize" 130    2
"Maize" 130    2
"Maize" 130  100
"Maize" 130    3
"Maize" 130    3
"Maize" 130    0
"Maize" 130  .95
"Maize" 130   50
"Maize" 130   36
"Maize" 130   20
"Maize" 130  200
"Maize" 130   10
"Maize" 130    1
"Maize" 130   50
"Maize" 130   30
"Maize" 130    6
"Maize" 130    1
"Maize" 130   40
"Maize" 130    1
"Maize" 130    1
end

------------------ copy up to and including the previous line ------------------

Listed 100 out of 9560 observations

[Bruce Weaver]

But I want to convert all the wheat using its crop code 111 to convert to kcal. The quantity of wheat is 20kg and I want to convert that to grams first by multiplying by 1000. Then I want to multiply 3.71kcal/gram for the conversion.

Where does the 3.71 come from? I don't see it in your data listing. This is why I think the first step you have to carry out is to bring in a new variable that gives the kcal/100g value for each crop code. Merging with a lookup table is a convenient way to do that, it seems to me.

[Mangji Zo]

Hi Bruce,

Thank you for the helpful feedback again! I tried your code to create a lookup table but it did not work for me since Stata is telling my that 740 is not a valid command?

[Bruce Weaver]

Mangji, please post your code for creating the lookup table.

[Mangji Zo]

Hi Bruce,

Here is my code:
sort Crop_code
input int Crop_kcal kcal100g
111 371
120 343
130 354
end


* Example generated by -dataex-. To install: ssc install dataex
clear
input str20 Crop_name int Crop_code float type_quan_crop int Crop_kcal float kcal100g
"Wheat" 111 20 . .
"Wheat" 111 0 . .
"Wheat" 111 25 . .
"Rice" 120 1 . .
"Rice" 120 4 . .
"Rice" 120 0 . .
"Rice" 120 1 . .
"Rice" 120 2 . .
"Rice" 120 5 . .
"Rice" 120 50 . .
"Rice" 120 520 . .
"Rice" 120 1 . .
"Rice" 120 0 . .
"Rice" 120 7 . .
"Rice" 120 100 . .
"Rice" 120 50 . .
"Rice" 120 2 . .
"Rice" 120 140 . .
"Rice" 120 2 . .
"Rice" 120 1 . .
"Rice" 120 8.5 . .
"Rice" 120 3 . .
"Rice" 120 0 . .
"Rice" 120 .95 . .
"Rice" 120 2 . .
"Rice" 120 1 . .
"Rice" 120 280 . .
"Rice" 120 .5 . .
"Rice" 120 0 . .
"Rice" 120 0 . .
"Rice" 120 10 . .
"Rice" 120 300 . .
"Rice" 120 70 . .
"Rice" 120 50 . .
"Rice" 120 100 . .
"Rice" 120 0 . .
"Rice" 120 30 . .
"Rice" 120 2 . .
"Rice" 120 2 . .
"Rice" 120 100 . .
"Rice" 120 90 . .
"Rice" 120 8 . .
"Rice" 120 100 . .
"Rice" 120 2 . .
"Rice" 120 2 . .
"Rice" 120 3.05 . .
"Rice" 120 300 . .
"Rice" 120 13 . .
"Rice" 120 .5 . .
"Rice" 120 2 . .
"Rice" 120 7 . .
"Rice" 120 6 . .
"Rice" 120 1 . .
"Rice" 120 11 . .
"Rice" 120 4 . .
"Rice" 120 100 . .
"Rice" 120 320 . .
"Rice" 120 100 . .
"Rice" 120 14 . .
"Rice" 120 200 . .
"Rice" 120 .1 . .
"Rice" 120 2 . .
"Rice" 120 1 . .
"Rice" 120 0 . .
"Maize" 130 7 . .
"Maize" 130 150 . .
"Maize" 130 6 . .
"Maize" 130 100 . .
"Maize" 130 1 . .
"Maize" 130 0 . .
"Maize" 130 .5 . .
"Maize" 130 1 . .
"Maize" 130 20 . .
"Maize" 130 1 . .
"Maize" 130 12 . .
"Maize" 130 1 . .
"Maize" 130 20 . .
"Maize" 130 3 . .
"Maize" 130 3 . .
"Maize" 130 150 . .
"Maize" 130 140 . .
"Maize" 130 3 . .
"Maize" 130 .5 . .
"Maize" 130 50 . .
"Maize" 130 .05 . .
"Maize" 130 36 . .
"Maize" 130 20 . .
"Maize" 130 14.3 . .
"Maize" 130 1 . .
"Maize" 130 3 . .
"Maize" 130 150 . .
"Maize" 130 5 . .
"Maize" 130 97.5 . .
"Maize" 130 3 . .
"Maize" 130 1 . .
"Maize" 130 6 . .
"Maize" 130 2 . .
"Maize" 130 100 . .
"Maize" 130 0 . .
"Maize" 130 2 . .
end
[/CODE]
------------------ copy up to and including the previous line ------------------

[Bruce Weaver]

So your ultimate goal is to compute a new variable showing the actual kcals, given the crop code and the kilograms? Try this.

First, create your lookup table and save it:

* Create a look-up table with the kcal/100g values by Crop_code.
* NOTE:  You must fill in the kcal100g values that are missing,
* because I do not know what they are!
clear *
input int Crop_code kcal100g
111 371
120 343
130 354
210 .
310 .
460 .
510 .
610 .
620 .
630 350
640 .
741 100
742 .
end
sort Crop_code
* Change file path as needed or desired.
save "C:/temp/kcal_lookup.dta", replace

Second, open your data file. I don't have your data file, so I'll use the sample you generated via -dataex- in #1 above.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str14 HHID str20 Crop_name int Crop_code float type_quan_crop
"1021000108"   "Banana food"    741  48
"1021000108"   "Cassava"        630   3
"1021000113"   "Beans"          210   3
"1021000113"   "Dodo"           460   2
"1021000113"   "Dodo"           460  10
"1021000113"   "Maize"          130   1
"1021000408"   "Beans"          210   2
"1021000408"   "Maize"          130   1
"1021000710"   "Beans"          210 3.5
"1021000710"   "Maize"          130  60
"1021000807"   "Banana food"    741 100
"1021000807"   "Banana food"    741  30
"1021000807"   "Banana food"    741   0
"1021000807"   "Cassava"        630   0
"102100080803" "Banana food"    741  20
"102100080803" "Beans"          210   1
"102100080803" "Maize"          130   0
"102100080803" "Groundnuts"     310   2
"102100080803" "Maize"          130   0
"102100080803" "Cassava"        630   2
"102100080803" "Maize"          130 1.5
"102100080803" "Sweet potatoes" 620   1
"102100110201" "Beans"          210   1
"102100110201" "Groundnuts"     310   3
"102100110201" "Groundnuts"     310   2
"102100110201" "Beans"          210   1
"1021001109"   "Sweet potatoes" 620  32
"1021001109"   "Banana food"    741  12
"1021001109"   "Cassava"        630   2
"1021001109"   "Maize"          130  .5
"1021001109"   "Yam"            640   4
"1021001109"   "Sugarcane"      510 120
"1021001304"   "Banana food"    741   4
"1021001304"   "Beans"          210   4
"1021001304"   "Beans"          210  10
"1021001304"   "Cassava"        630   5
"1021001304"   "Maize"          130   1
"1021002610"   "Banana food"    741  10
"1021002610"   "Sweet potatoes" 620   5
"1021002610"   "Yam"            640   5
"1021002610"   "Groundnuts"     310   1
"1021002610"   "Maize"          130   1
"1021002610"   "Sweet potatoes" 620   2
"1021002611"   "Maize"          130   1
"1021002611"   "Maize"          130   0
"1021002810"   "Beans"          210  .5
"1021002810"   "Maize"          130   2
"1033000301"   "Banana food"    741  60
"1033000301"   "Maize"          130  15
"1033000302"   "Beans"          210   2
"1033000302"   "Banana food"    741  10
"1033000302"   "Beans"          210   1
"1033000303"   "Maize"          130   1
"1033000303"   "Banana food"    741  35
"1033000304"   "Banana food"    741  40
"1033000304"   "Banana beer"    742  30
"1033000304"   "Beans"          210   1
"1033000304"   "Maize"          130  20
"103300030403" "Banana food"    741   5
"103300030403" "Banana food"    741   6
"103300030403" "Beans"          210   1
"103300030403" "Maize"          130  .5
"1033000307"   "Banana beer"    742  30
"1033000307"   "Banana beer"    742  10
"1033000307"   "Banana food"    741 130
"1033000308"   "Maize"          130   0
"1033000308"   "Beans"          210   4
"1033000308"   "Irish potatoes" 610   2
"1033000308"   "Maize"          130   0
"1033000309"   "Banana food"    741   7
"1033000309"   "Banana food"    741  30
"1033000310"   "Banana food"    741  20
"1033000504"   "Banana food"    741  48
"1033000504"   "Beans"          210   1
"1033000504"   "Maize"          130   1
"1033000504"   "Beans"          210   1
"1033000505"   "Banana food"    741  26
"1033000505"   "Beans"          210   5
"1033000506"   "Beans"          210   1
"1033000506"   "Maize"          130   2
"1033000506"   "Groundnuts"     310   3
"1033000506"   "Irish potatoes" 610   3
"1033000507"   "Beans"          210   2
"1033000507"   "Cassava"        630   6
"1033000509"   "Beans"          210   3
"1033000510"   "Sweet potatoes" 620   2
"1033000510"   "Beans"          210   4
"1033000510"   "Maize"          130   1
"1033000510"   "Beans"          210   4
"1033000510"   "Maize"          130   6
"1033000511"   "Maize"          130   0
"1033000511"   "Beans"          210   1
"1033000511"   "Cassava"        630   5
"1033000511"   "Maize"          130   0
"1033000511"   "Beans"          210  .5
"1033000511"   "Maize"          130   2
"103300051102" "Banana beer"    742  18
"103300051102" "Banana food"    741  30
"1041000210"   "Maize"          130  13
"1041000210"   "Sweet potatoes" 620   1
end

Third, use -merge- to bring the kcal100g variable into your main dataset:

* Bring in the kcal100g variable via m:1 merge with the lookup table
sort Crop_code
merge m:1 Crop_code using "C:/temp/kcal_lookup.dta"

Fourth, carry out your computation of new variable kcals. If I follow, it will look like this:

* g = gram, hg = hectogram, kg = kilogram
* 1 hg = 100 g; 10 hg = 1000 g = 1 kg
* kcal100g = kcal per hg
* type_quan_crop = kg; multiply by 10 to get weight in hectograms
generate kcals = type_quan_crop*10 * kcal100g
summarize kcals

HTH.

[Mangji Zo]

Thank you, Bruce for teaching me how to create a look up table! I have never made one before! It worked! Thank you for the codes!