How to Construct Daily Food Consumption Score

[Charles Ackah]

Dear Statalisters,
I am working with a dataset that contains information on household consumption of 10 food categories as attached below:

Variable Description
itemcode Categorical variable indicating the 10 food groups consumed by a household

y4_hhid Unique household identifier

hh_j08_3 Number of days in a week a household consumed a given food category


I want to create unique household variables that capture households' consumption of each food category (reduced to 8 food groups) using the itemcode & hh_j08_3 variables as follows:

FCSStap "Consumption over the past 7 days (cereals and tubers)" (Category 1 & 2 combined)
FCSPulse "Consumption over the past 7 days (pulses)" (Category 3)
FCSVeg "Consumption over the past 7 days (vegetables)" (Category 4)
FCSPr "Consumption over the past 7 days (protein-rich foods)" (Category 5)
FCSFruit "Consumption over the past 7 days (fruit)" (Category 6)
FCSDairy "Consumption over the past 7 days (dairy products)" (Category 7)
FCSFat "Consumption over the past 7 days (oil)" (Category 8)
FCSSugar "Consumption over the past 7 days (sugar)" (Category 9)
(Category 10 is set to missing)

I need help with a code to accomplish that. Thanks very much in anticipation.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str8 y4_hhid byte(itemcode hh_j08_3)
"1000-001"  1 7
"1000-001"  2 2
"1000-001"  3 3
"1000-001"  4 7
"1000-001"  5 2
"1000-001"  6 0
"1000-001"  7 1
"1000-001"  8 7
"1000-001"  9 7
"1000-001" 10 7
"1001-001"  1 7
"1001-001"  2 0
"1001-001"  3 3
"1001-001"  4 7
"1001-001"  5 2
"1001-001"  6 0
"1001-001"  7 7
"1001-001"  8 7
"1001-001"  9 7
"1001-001" 10 7
"1002-001"  1 7
"1002-001"  2 0
"1002-001"  3 7
"1002-001"  4 7
"1002-001"  5 0
"1002-001"  6 0
"1002-001"  7 7
"1002-001"  8 7
"1002-001"  9 7
"1002-001" 10 7
"1003-001"  1 7
"1003-001"  2 0
"1003-001"  3 5
"1003-001"  4 3
"1003-001"  5 1
"1003-001"  6 0
"1003-001"  7 7
"1003-001"  8 7
"1003-001"  9 7
"1003-001" 10 7
"1005-001"  1 7
"1005-001"  2 0
"1005-001"  3 1
"1005-001"  4 7
"1005-001"  5 2
"1005-001"  6 0
"1005-001"  7 7
"1005-001"  8 7
"1005-001"  9 7
"1005-001" 10 7
"1006-001"  1 7
"1006-001"  2 0
"1006-001"  3 5
"1006-001"  4 6
"1006-001"  5 1
"1006-001"  6 3
"1006-001"  7 7
"1006-001"  8 7
"1006-001"  9 7
"1006-001" 10 7
"1007-001"  1 7
"1007-001"  2 0
"1007-001"  3 7
"1007-001"  4 7
"1007-001"  5 2
"1007-001"  6 0
"1007-001"  7 7
"1007-001"  8 7
"1007-001"  9 7
"1007-001" 10 7
"1009-001"  1 7
"1009-001"  2 0
"1009-001"  3 3
"1009-001"  4 7
"1009-001"  5 2
"1009-001"  6 0
"1009-001"  7 7
"1009-001"  8 7
"1009-001"  9 7
"1009-001" 10 7
"1019-001"  1 7
"1019-001"  2 0
"1019-001"  3 2
"1019-001"  4 7
"1019-001"  5 3
"1019-001"  6 7
"1019-001"  7 0
"1019-001"  8 7
"1019-001"  9 7
"1019-001" 10 7
"1020-001"  1 7
"1020-001"  2 2
"1020-001"  3 2
"1020-001"  4 7
"1020-001"  5 2
"1020-001"  6 7
"1020-001"  7 7
"1020-001"  8 7
"1020-001"  9 7
"1020-001" 10 7
end
label values itemcode HH_J08_1
label def HH_J08_1 1 "A. CEREALS, GRAINS AND CEREAL PRODUCTS", modify
label def HH_J08_1 2 "B. ROOTS, TUBERS, AND PLANTAINS", modify
label def HH_J08_1 3 "C. NUTS AND PULSES", modify
label def HH_J08_1 4 "D. VEGETABLES", modify
label def HH_J08_1 5 "E. MEAT, FISH AND ANIMAL PRODUCTS", modify
label def HH_J08_1 6 "F. FRUITS", modify
label def HH_J08_1 7 "G. MILK/MILK PRODUCTS", modify
label def HH_J08_1 8 "H. FATS/OIL", modify
label def HH_J08_1 9 "I. SUGAR/SUGAR PRODUCTS/HONEY", modify
label def HH_J08_1 10 "J. SPICES/CONDIMENTS", modify

[Ken Chui]

This may do what you need. You'd just need to rename the other 7 variables. Categories 1 and 2 are combined and created. Item code 10 is dropped.

collapse (sum) hh_j08_3, by(y4_hhid itemcode)
drop if itemcode == 10
reshape wide hh_j08_3, i(y4_hhid) j(itemcode)
egen FCSStap = rowtotal(hh_j08_31 hh_j08_32)
drop hh_j08_31 hh_j08_32
order FCSStap, after(y4_hhid)

[Charles Ackah]

Ken Chui thank you very much. This is extremely helpful.