Dear Statalisters,


I have a cross section dataset which contains information on household level food consumption. Below is an example of the dataset.
Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input double Hhs_no byte Time_of_day int menu double Menu_wht int Ingredient_1 double Ingredient_wht_1 int Ingredient_2 double Ingredient_wht_2 int Ingredient_3 double Ingredient_wht_3 int Ingredient_4 double Ingredient_wht_4
1 3  315   10  315   10   .   .   .   .   .  .
1 3  316    8  316    8   .   .   .   .   .  .
1 3 2522    9 2522    9   .   .   .   .   .  .
1 1 2771 1250    3  500   .   .   .   .   .  .
1 2 2771 2500    3 1000   .   .   .   .   .  .
1 2 2891 1404   97  250 198 500  61 300  64 40
1 1 2901  579   61  300  64  42  65  19 248  4
2 3 2522   14 2522   14   .   .   .   .   .  .
2 1 2771 1200    1  400   .   .   .   .   .  .
2 1 2772 1500    1  500   .   .   .   .   .  .
2 2 2773 6000    1 2000   .   .   .   .   .  .
2 1 2901  999   47  500 228 200  64  48 248  3
2 2 2902 3027   50 2500 214 250  64  10 248  4
3 1 2771 1000    3  400   .   .   .   .   .  .
3 1 2901  328   77   45  61  60 176  20  64 20
1 3 2771 1250    3  500   .   .   .   .   .  .
end
label values Time_of_day x1_03
label def x1_03 1 "Morning", modify
label def x1_03 2 "Noon", modify
label def x1_03 3 "Night", modify
label values menu x1_05
label def x1_05 315 "Betel Leaf", modify
label def x1_05 316 "Supari", modify
label def x1_05 2522 "Salt (Iodine)", modify
label def x1_05 2771 "Rice/Jao", modify
label def x1_05 2772 "Rice/Jao", modify
label def x1_05 2773 "Rice/Jao", modify
label def x1_05 2891 "Jhol curry", modify
label def x1_05 2901 "Bhuna curry", modify
label def x1_05 2902 "Bhuna curry", modify
label values Ingredient_1 x1_07_01
label def x1_07_01 1 "Parboiled rice (coarse)", modify
label def x1_07_01 3 "Fine rice", modify
label def x1_07_01 47 "Sweet gourd", modify
label def x1_07_01 50 "Water gourd", modify
label def x1_07_01 61 "Potato", modify
label def x1_07_01 77 "Beher gura", modify
label def x1_07_01 97 "Lau Shak", modify
label def x1_07_01 315 "Betel Leaf", modify
label def x1_07_01 316 "Supari", modify
label def x1_07_01 2522 "Salt (Iodine)", modify
label values Ingredient_2 x1_07_02
label def x1_07_02 61 "Potato", modify
label def x1_07_02 64 "Onion", modify
label def x1_07_02 198 "Koi", modify
label def x1_07_02 214 "Tengra", modify
label def x1_07_02 228 "Bele", modify
label values Ingredient_3 x1_07_03
label def x1_07_03 61 "Potato", modify
label def x1_07_03 64 "Onion", modify
label def x1_07_03 65 "Garlic", modify
label def x1_07_03 176 "Rui", modify
label values Ingredient_4 x1_07_04
label def x1_07_04 64 "Onion", modify
label def x1_07_04 248 "Turmeric (dried)", modify
Where Hhs_no refers to the household identification number, Time refers to the time of day that the meal was consumed, menu refers to the composite food item, Menu_wht refers to the weight of the cooked food ingredients_1-ingredient_4 refers to the ingredients used in preparing the food item, Ingredient_wht_1- Ingredient_wht_4 refers to the weight of the ingredients used in preparing the food. Some households consumed the same food items more than once a day. So my data set is unique by Hhs_no, Menu and Time_of_day but not by Hhs_no and Menu
I need to arrange this data in a way that there is no duplication for Hhs_no and menu. An example of how I need the data to look is below:
Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input double Hhs_no byte Time_of_day int menu double Menu_wht int Ingredient_1 double Ingredient_wht_1 int Ingredient_2 double Ingredient_wht_2 int Ingredient_3 double Ingredient_wht_3 int Ingredient_4 double Ingredient_wht_4
1 3  315   10  315   10   .   .   .   .   .  .
1 3  316    8  316    8   .   .   .   .   .  .
1 3 2522    9 2522    9   .   .   .   .   .  .
1 1 2771 1250    3  500   .   .   .   .   .  .
1 2 2891 1404   97  250 198 500  61 300  64 40
1 1 2901  579   61  300  64  42  65  19 248  4
2 3 2522   14 2522   14   .   .   .   .   .  .
2 1 2771 1200    1  400   .   .   .   .   .  .
2 1 2772 1500    1  500   .   .   .   .   .  .
2 2 2773 6000    1 2000   .   .   .   .   .  .
2 1 2901  999   47  500 228 200  64  48 248  3
2 2 2902 3027   50 2500 214 250  64  10 248  4
3 1 2771 1000    3  400   .   .   .   .   .  .
3 1 2901  328   77   45  61  60 176  20  64 20
end
label values Time_of_day x1_03
label def x1_03 1 "Morning", modify
label def x1_03 2 "Noon", modify
label def x1_03 3 "Night", modify
label values menu x1_05
label def x1_05 315 "Betel Leaf", modify
label def x1_05 316 "Supari", modify
label def x1_05 2522 "Salt (Iodine)", modify
label def x1_05 2771 "Rice/Jao", modify
label def x1_05 2772 "Rice/Jao", modify
label def x1_05 2773 "Rice/Jao", modify
label def x1_05 2891 "Jhol curry", modify
label def x1_05 2901 "Bhuna curry", modify
label def x1_05 2902 "Bhuna curry", modify
label values Ingredient_1 x1_07_01
label def x1_07_01 1 "Parboiled rice (coarse)", modify
label def x1_07_01 3 "Fine rice", modify
label def x1_07_01 47 "Sweet gourd", modify
label def x1_07_01 50 "Water gourd", modify
label def x1_07_01 61 "Potato", modify
label def x1_07_01 77 "Beher gura", modify
label def x1_07_01 97 "Lau Shak", modify
label def x1_07_01 315 "Betel Leaf", modify
label def x1_07_01 316 "Supari", modify
label def x1_07_01 2522 "Salt (Iodine)", modify
label values Ingredient_2 x1_07_02
label def x1_07_02 61 "Potato", modify
label def x1_07_02 64 "Onion", modify
label def x1_07_02 198 "Koi", modify
label def x1_07_02 214 "Tengra", modify
label def x1_07_02 228 "Bele", modify
label values Ingredient_3 x1_07_03
label def x1_07_03 61 "Potato", modify
label def x1_07_03 64 "Onion", modify
label def x1_07_03 65 "Garlic", modify
label def x1_07_03 176 "Rui", modify
label values Ingredient_4 x1_07_04
label def x1_07_04 64 "Onion", modify
label def x1_07_04 248 "Turmeric (dried)", modify
I guess that I need to use the collapse and reshape commands but am not sure how. Any help would be appreciated.
Thanks