[Mangji Zo]

Hi Carole,

Thank you! It worked! Is there any way to combine the two Crop_namecodescat variables and the two tot_sold_2 variables to match each of the HHID number?

* Example generated by -dataex-. To install: ssc install dataex
clear
input str14 HHID double(Crop_namecodescat Crop_namecodescatB tot_sold_2B tot_sold_2)
"1013000204"   . 1                1.5                  .
"1021000113"   1 1                  3                 16
"102100080803" 1 1               24.5               27.5
"102100110201" 1 .                  .                  7
"1021001304"   1 1               28.5                 24
"1021002610"   1 1                  6                 24
"1033000301"   1 1                 17                 75
"1033000302"   1 1                 36                 13
"1033000303"   1 1                 88                 36
"1033000304"   1 1                231                 91
"103300030403" 1 1                 24               12.5
"1033000305"   . 1               46.5                  .
"1033000307"   1 1                  1                170
"1033000309"   1 .                  .                 37
"1033000310"   1 1                 11                 20
"1033000506"   1 1               70.5                  9
"1033000510"   1 1                 24                 17
"1033000511"   1 1                 18                8.5
"1041000210"   1 1                 85                 15
"1043000402"   1 1                 15                  8
"1043000403"   1 1                118               83.5
"1043000406"   1 1                 12                1.5
"1043000408"   1 .                  .                  5
"1043000409"   1 1                 57 22.200000000000003
"104300040908" 1 1  93.79999999999998                 72
"1043000601"   1 1               80.5                  4
"1043000602"   1 1               63.4                 98
"1043000603"   1 1                471              152.5
"1043000607"   1 1                 60                 25
"1043000610"   1 1                 61                  4
"1043000611"   1 1                9.5                 16
"1043000804"   1 1                 14                 20
"1043000805"   1 1                 21                  0
"1043000806"   1 1                115                 31
"1043000807"   1 1                  7                 52
"1043000809"   1 1                3.5                2.5
"1043000810"   1 1                 15                  6
"1043000811"   1 1                  7                  7
"1051000110"   1 1                 40                 38
"105300040605" 1 1                 47                 34
"1053000410"   1 1                  2                  4
"1053000802"   1 1               40.5                 50
"1053000804"   1 1                 18                 20
"1053000805"   1 1               85.5                1.5
"1053000806"   1 1                 57                 33
"1053000808"   1 1              536.5                359
"1053001205"   1 1                 80               66.5
"1053001206"   1 1                3.5                  0
"1053001208"   1 1                 88                140
"1053001210"   1 1                 23                  0
"1053001503"   1 1                 45                 72
"1053001505"   1 1                360              385.5
"1053001507"   1 1                151                266
"1053001508"   1 1                 60                104
"1053001509"   1 1                 58                338
"1053001512"   1 1                 48                 98
"1053001513"   1 1 170.59999999999997               13.5
"105300151303" 1 1                217                121
"1053001902"   1 1                  5                  7
"1053001903"   1 1                 91                 40
"1053001905"   1 1                 68                144
"1053001907"   1 1                353                270
"1053001910"   1 1               45.8                 13
"1053002302"   1 1                 20               54.5
"1053002303"   1 1                 79                 16
"1053002305"   . 1                 84                  .
"1053002306"   1 1              15.75                 58
"1053002308"   1 1               17.5                238
"1053002310"   1 1               21.5                171
"1053002602"   1 1                 33                  0
"105300260602" 1 1                 28                 52
"1053002609"   1 1                 86                126
"1053003003"   1 1                  5                4.1
"1053003004"   1 1 14.199999994039533                 13
"1053003008"   1 1                  3  .5999999999999996
"1053003010"   1 1               80.5                 42
"1053003303"   1 1                 32                 34
"1053003304"   1 1                 74                 77
"1053003307"   1 1                115               43.5
"1053003309"   1 1                 34                 26
"1053003312"   1 1               24.5                 83
"105300331202" 1 1                 17                 12
"1063000304"   1 1               53.5                 10
"106300030511" 1 1                  7                 29
"106300030512" 1 1              142.5                135
"1063000307"   1 1 17.799999904632568                  0
"1063000308"   1 1              205.5                  8
"1063000310"   1 1                213                442
"1063000311"   1 1                155                  3
"1063000502"   1 1                  5                  0
"1063000504"   1 1                5.5                 26
"1063000506"   . 1                 64                  .
"106300050604" 1 1                 20                 65
"1063000508"   1 1                  9                  8
"1063000509"   1 1               91.6                  0
"106300051007" 1 .                  .                 12
"106300051008" 1 1                 29                1.5
"1063000511"   1 1                 14                  0
"1063000702"   1 1                8.5                 12
"1063000706"   1 1                 69                 84
end
label values Crop_namecodescat Crop_namecodescatlab
label def Crop_namecodescatlab 1 "Food Crop", modify
label values Crop_namecodescatB Crop_namecodescatBlab
label def Crop_namecodescatBlab 1 "Food Crop", modify

[Nick Cox]

I don't understand what is wanted in #16. "combine" could mean several things, including "add". I'd give a worked example if you don't get a better answer.

A comment on #14 and #15. You can also do this fairly directly with official code.

egen tag = tag(cropname HHID)  
 bysort HHID: egen num_crop= total(tag)

See https://www.stata-journal.com/sjpdf....iclenum=dm0042 p.563 (including the comment on missing values).

[Mangji Zo]

Hi Nick,

Thank you for the feedback! Sorry for the lack of clarity with the question. I want to add the variables tot_sold_2B and tot_sold_2 together for each of the HHID. I also want to combine the Crop_namecodescat and Crop_namecodescatB into one column if possible for each HHID? I merged the data initially.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str14 HHID double(Crop_namecodescat tot_sold_2 Crop_namecodescatB tot_sold_2B) byte _merge
"1013000204"   .                  . 1                1.5 2
"1021000108"   1                 51 1                 61 3
"1021000113"   1                 16 1                  3 3
"1021000408"   1                  3 .                  . 1
"1021000710"   1               63.5 1                 40 3
"1021000807"   1                130 1                 65 3
"102100080803" 1               27.5 1               24.5 3
"102100110201" 1                  7 .                  . 1
"1021001109"   1              170.5 1               25.5 3
"1021001304"   1                 24 1               28.5 3
"1021002610"   1                 24 1                  6 3
"1021002611"   1                  1 1                1.5 3
"1021002810"   1                2.5 1                  3 3
"1033000301"   1                 75 1                 17 3
"1033000302"   1                 13 1                 36 3
"1033000303"   1                 36 1                 88 3
"1033000304"   1                 91 1                231 3
"103300030403" 1               12.5 1                 24 3
"1033000305"   .                  . 1               46.5 2
"1033000307"   1                170 1                  1 3
"103300030702" .                  . 1                 16 2
"1033000308"   1                  6 1                 37 3
"1033000309"   1                 37 .                  . 1
"1033000310"   1                 20 1                 11 3
"1033000504"   1                 51 1                 56 3
"1033000505"   1                 31 1                 62 3
"1033000506"   1                  9 1               70.5 3
"1033000507"   1                  8 1                  3 3
"1033000509"   1                  3 1                  5 3
"1033000510"   1                 17 1                 24 3
"1033000511"   1                8.5 1                 18 3
"103300051102" 1                 48 1               64.5 3
"1041000207"   1                  0 1                 16 3
"1041000210"   1                 15 1                 85 3
"1043000402"   1                  8 1                 15 3
"1043000403"   1               83.5 1                118 3
"1043000406"   1                1.5 1                 12 3
"1043000407"   1                 54 1              161.5 3
"1043000408"   1                  5 .                  . 1
"1043000409"   1 22.200000000000003 1                 57 3
"104300040906" 1                503 1                674 3
"104300040908" 1                 72 1  93.79999999999998 3
"1043000410"   1                355 1                 58 3
"1043000601"   1                  4 1               80.5 3
"1043000602"   1                 98 1               63.4 3
"1043000603"   1              152.5 1                471 3
"1043000604"   1               11.5 1                 58 3
"1043000605"   1                  2 1                  4 3
"1043000607"   1                 25 1                 60 3
"1043000610"   1                  4 1                 61 3
"1043000611"   1                 16 1                9.5 3
"1043000804"   1                 20 1                 14 3
"1043000805"   1                  0 1                 21 3
"1043000806"   1                 31 1                115 3
"104300080608" 1                 29 .                  . 1
"1043000807"   1                 52 1                  7 3
"1043000808"   1                 44 1               72.5 3
"1043000809"   1                2.5 1                3.5 3
"1043000810"   1                  6 1                 15 3
"1043000811"   1                  7 1                  7 3
"1051000104"   1                155 1                9.4 3
"1051000109"   1                 12 1                  0 3
"1051000110"   1                 38 1                 40 3
"1051000301"   1                  0 .                  . 1
"1051000310"   1                 44 1                 34 3
"1053000402"   1                  0 1                  0 3
"1053000403"   1               13.5 1                 17 3
"1053000404"   1                  9 1                 17 3
"1053000406"   .                  . 1                 .5 2
"105300040605" 1                 34 1                 47 3
"105300040607" 1                 22 1                 27 3
"1053000407"   1                 72 1               26.7 3
"1053000408"   1                 12 1                  4 3
"1053000409"   1              106.5 1                165 3
"1053000410"   1                  4 1                  2 3
"1053000802"   1                 50 1               40.5 3
"1053000804"   1                 20 1                 18 3
"1053000805"   1                1.5 1               85.5 3
"1053000806"   1                 33 1                 57 3
"1053000807"   1                 57 1                134 3
"1053000808"   1                359 1              536.5 3
"1053000809"   1                  0 1  77.60000000000001 3
"1053001202"   1                  4 1                  8 3
"1053001203"   1 63.900000000000006 1                 58 3
"1053001204"   1                734 1                181 3
"1053001205"   1               66.5 1                 80 3
"1053001206"   1                  0 1                3.5 3
"1053001207"   1              19.17 1                  9 3
"1053001208"   1                140 1                 88 3
"1053001209"   1                 27 1                  6 3
"1053001210"   1                  0 1                 23 3
"1053001503"   1                 72 1                 45 3
"1053001505"   1              385.5 1                360 3
"1053001507"   1                266 1                151 3
"1053001508"   1                104 1                 60 3
"1053001509"   1                338 1                 58 3
"1053001510"   1                400 1                413 3
"1053001512"   1                 98 1                 48 3
"1053001513"   1               13.5 1 170.59999999999997 3
"105300151301" 1                 58 1 130.70000000000005 3
end
label values Crop_namecodescat Crop_namecodescatlab
label def Crop_namecodescatlab 1 "Food Crop", modify
label values Crop_namecodescatB Crop_namecodescatBlab
label def Crop_namecodescatBlab 1 "Food Crop", modify
label values _merge _merge
label def _merge 1 "master only (1)", modify
label def _merge 2 "using only (2)", modify
label def _merge 3 "matched (3)", modify

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

Listed 100 out of 2322 observations
Use the count() option to list more

[Nick Cox]

You surely know how to add variables:

gen z = x + y

Otherwise, same question about "combine"; what happens with 1 . or . 1 as pairs of values? You have to tell us your rules.

[Mangji Zo]

Hi Nick,

Thanks for your help! If I use gen z= x+y to add tot_sold_2 and tot_sold_2B together, sometimes there is missing data in tot_sold_2B and not in tot_sold_2. But when I add them together, they are classified as missing values. Do I change the all the missing data in tot_sold_2 and tot_sold_2B to zero? I want the total amount of tot_sold_2 and tot_sold_2B for each HHID. Thanks!

[Rich Goldstein]

if you want the missing values treated as 0's, use -egen- with the "rowtotal" function; see

help egen

[Mangji Zo]

Hi,

I would really like some advice on how to use STATA for observations from two different variables that identifies an individual in a household (HHID) given the following information:

How do I multiply the number of household members in each category (identified by two variables: sex and age except children younger than 5 years old which is identified by just age) by the different energy requirement in each category (above) and then divided by 2,900 kcal?



I have generated age categories (hh_agecat) for the above and calculated the number of members in each age-sex category in each household (HHID) by using the following code (am I on the right track?):

gen hh_agecat = hh_age
recode hh_agecat 0/1 = 1 1/2 = 2 2/3 = 3 3/5 = 4 5/7 = 5 7/10= 6 10/12= 7 12/14= 8 14/16= 9 16/18= 10 18/30 =11 30/60=12 60/max= 13
label define hh_agecatlab 1 "<1" 2 "1-2" 3 "2-3" 4 "3-5" 5 "5-7" 6 "7-10" 7 "10=12" 8 "12-14" 9"14-16" 10 "16-18" 11 "18-30" 12 "30-60" 13 "60+"
label var hh_agecat 13agecat

bysort HHID: egen age_sexcat=nvals(sex hh_agecat)


How do I calculate the conversion for each individual according to their sex and age since each individual differs in each household?

* Example generated by -dataex-. To install: ssc install dataex
clear
input str12 HHID byte(sex hh_age) float(hh_agecat age_sexcat)
"1013000201"   2 28 11  6
"1013000201"   1 17 10  6
"1013000201"   1  6  5  6
"1013000201"   1 11  7  6
"1013000201"   1  9  6  6
"1013000201"   2 44 12  6
"1013000204"   2  6  5  5
"1013000204"   2  0  1  5
"1013000204"   2  2  2  5
"1013000204"   2 32 12  5
"1013000204"   1 34 12  5
"1013000206"   1 29 11  1
"1013000210"   1 38 12  1
"1013000213"   1 31 12  1
"101300021302" 1  5  4  4
"101300021302" 1 35 12  4
"101300021302" 1  0  1  4
"101300021302" 2 28 11  4
"1021000102"   2 19 11  5
"1021000102"   2 10  6  5
"1021000102"   1 39 12  5
"1021000102"   2  2  2  5
"1021000102"   2 19 11  5
"1021000102"   2 37 12  5
"1021000108"   1 23 11  5
"1021000108"   2 25 11  5
"1021000108"   2 17 10  5
"1021000108"   1 20 11  5
"1021000108"   2 59 12  5
"1021000108"   1 70 13  5
"1021000109"   2 16  9  6
"1021000109"   2  1  1  6
"1021000109"   2 22 11  6
"1021000109"   1 12  7  6
"1021000109"   1 19 11  6
"1021000109"   2 39 12  6
"1021000110"   2 44 12  4
"1021000110"   1 25 11  4
"1021000110"   2 19 11  4
"1021000110"   1 23 11  4
"1021000110"   1 21 11  4
"1021000110"   1 53 12  4
"1021000111"   1 15  9  8
"1021000111"   2 17 10  8
"1021000111"   1 18 10  8
"1021000111"   1 16  9  8
"1021000111"   1 15  9  8
"1021000111"   2 20 11  8
"1021000111"   1 25 11  8
"1021000111"   2  5  4  8
"1021000111"   1 13  8  8
"1021000111"   1 47 12  8
"1021000113"   2 17 10  8
"1021000113"   2 43 12  8
"1021000113"   1  4  4  8
"1021000113"   1 14  8  8
"1021000113"   1 10  6  8
"1021000113"   1 47 12  8
"1021000113"   2 12  7  8
"1021000113"   2 32 12  8
"1021000113"   1 20 11  8
"1021000201"   1 14  8  5
"1021000201"   1 41 12  5
"1021000201"   2 16  9  5
"1021000201"   2 38 12  5
"1021000201"   2  9  6  5
"1021000202"   1 43 12  3
"1021000202"   2 14  8  3
"1021000202"   2 20 11  3
"1021000203"   1  7  5 10
"1021000203"   1 12  7 10
"1021000203"   1 14  8 10
"1021000203"   2 17 10 10
"1021000203"   1 20 11 10
"1021000203"   2 27 11 10
"1021000203"   1 37 12 10
"1021000203"   2  4  4 10
"1021000203"   1  2  2 10
"1021000203"   2 58 12 10
"102100020304" 1 27 11  4
"102100020304" 1  3  3  4
"102100020304" 1  6  5  4
"102100020304" 2 24 11  4
"1021000207"   1 13  8  4
"1021000207"   2 38 12  4
"1021000207"   2 17 10  4
"1021000207"   1 41 12  4
"1021000209"   1 59 12  1
"1021000210"   1 35 12  1
"1021000212"   2 17 10  4
"1021000212"   1  4  4  4
"1021000212"   1 15  9  4
"1021000212"   2 48 12  4
"1021000213"   2  5  4  8
"1021000213"   2 20 11  8
"1021000213"   2 17 10  8
"1021000213"   1 25 11  8
"1021000213"   2 42 12  8
"1021000213"   2 21 11  8
"1021000213"   1 22 11  8
end
label values sex H2Q3
label def H2Q3 1 "MALE", modify
label def H2Q3 2 "FEMALE", modify

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

Listed 100 out of 17673 observations