Hi, I want to calculate per-capita expenditures by each household for each itc (item).
Finally I want to make the table which is attached as screenshot (code=itc). Array

I am running following code for each itc separately, but it is very time consuming, as i have to use keep command to drop all other items first.

Code:
 egen item=rsum(v1 v2 v3 v4)
replace item=item*2.17
collapse (sum) item (mean) itc, by (hhcode)
sort hhcode
merge 1:1 hhcode using "hhsize.dta"
drop _merge
replace item=0 if item==.
gen pcap= item/hhsize
gen pweight=hhsize*weight
label values itc itc
summ pcap [aweight=pweight]
tabstat pcap [aweight=pweight],  stat(mean)

The two data file are as follows;
Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input double hhcode int itc float q1 long v1 float q2 long v2 float q3 long v3 float q4 long v4
1111100101 1000    .  1575 . .   .  150    . 740
1111100101 1001    .   720 . .   .    .    . 420
1111100101 1002    .   855 . .   .  150    .   .
1111100101 1003    .     . . .   .    .    . 320
1111100101 1102    .     . . .   .    .    2  60
1111100101 1104 1000   400 . .   .    .    .   .
1111100101 1201    1   320 . .   .    .    .   .
1111100101 1204    .     . . .   .    .    5  40
1111100101 1305    .     . . .   .    .    2 160
1111100101 1309    .     . . .   .    .    4 160
1111100101 1402    .     . . . 100  150    .   .
1111100101 1501    5   250 . .   .    .    .   .
1111100101 1502    3   240 . .   .    .    .   .
1111100101 1503    2   160 . .   .    .    .   .
1111100101 1505    1    60 . .   .    .    .   .
1111100101 1602  1.5    45 . .   .    .    .   .
1111100101 1608  125    40 . .   .    .    .   .
1111100101 1701    1    60 . .   .    .    .   .
1111100101 1902    .     . . .   .    .    . 320
1111100101 2000    .  5515 . .   .  300    . 160
1111100101 2001    .  4710 . .   .  300    .   .
1111100101 2002    .   805 . .   .    .    . 160
1111100101 2101   50  2000 . .   .    .    .   .
1111100101 2102   15   600 . .   .    .    .   .
1111100101 2103    .     . . .  10  300    .   .
1111100101 2202    1    90 . .   .    .    .   .
1111100101 2206    2   220 . .   .    .    .   .
1111100101 2302    8  1240 . .   .    .    .   .
1111100101 2401 1000   510 . .   .    .    .   .
1111100101 2501  200    50 . .   .    .    .   .
1111100101 2701   40   320 . .   .    .    .   .
1111100101 2708    .   100 . .   .    .    .   .
1111100101 2709    .    20 . .   .    .    .   .
1111100101 2710    .     . . .   .    .   20 160
1111100101 2801    .    45 . .   .    .    .   .
1111100101 2803    .    80 . .   .    .    .   .
1111100101 2804    .    20 . .   .    .    .   .
1111100101 2805    .    40 . .   .    .    .   .
1111100101 2901    .    40 . .   .    .    .   .
1111100101 3001    .   110 . .   .    .    .   .
1111100101 3002    .    30 . .   .    .    .   .
1111100101 4000    .  2325 . .   .    .    .   .
1111100101 4304    .  1250 . .   .    .    .   .
1111100101 4404    .   175 . .   .    .    .   .
1111100101 4406    .   300 . .   .    .    .   .
1111100101 4407    .   400 . .   .    .    .   .
1111100101 4408    .   200 . .   .    .    .   .
1111100101 5000    . 41930 . .   . 3600    .   .
1111100101 5001    . 34390 . .   . 3600    .   .
1111100101 5002    .  7540 . .   .    .    .   .
1111100101 5101    . 21500 . .   .    .    .   .
1111100101 5102    .   200 . .   .    .    .   .
1111100101 5103    .  1800 . .   .    .    .   .
1111100101 5104    .  1200 . .   .    .    .   .
1111100101 5105    .   200 . .   .    .    .   .
1111100101 5106    .  4600 . .   .    .    .   .
1111100101 5201    .  3600 . .   .    .    .   .
1111100101 5202    .   120 . .   .    .    .   .
1111100101 5302    .   140 . .   .    .    .   .
1111100101 5404    .     . . .   . 3600    .   .
1111100101 5406    .   600 . .   .    .    .   .
1111100101 5501    .   310 . .   .    .    .   .
1111100101 5503    .   120 . .   .    .    .   .
1111100101 5602    .  1850 . .   .    .    .   .
1111100101 5603    .  1620 . .   .    .    .   .
1111100101 5604   40   110 . .   .    .    .   .
1111100101 5801    .   200 . .   .    .    .   .
1111100101 5802    .   460 . .   .    .    .   .
1111100101 5803    .  2500 . .   .    .    .   .
1111100101 5805    .   300 . .   .    .    .   .
1111100101 5902    .   500 . .   .    .    .   .
1111100101 6000    .  2560 . .   .    .    .   .
1111100101 6102    .   800 . .   .    .    .   .
1111100101 6103    .   350 . .   .    .    .   .
1111100101 6104    .    60 . .   .    .    .   .
1111100101 6203    .   150 . .   .    .    .   .
1111100101 6305    .  1200 . .   .    .    .   .
1111100102 1000    .  1121 . .   . 1410    . 225
1111100102 1001    .   300 . .   . 1410    .   .
1111100102 1002    .   746 . .   .    .    . 225
1111100102 1003    .    75 . .   .    .    .   .
1111100102 1101    .     . . .   7  560    .   .
1111100102 1107    .     . . .   8  640    .   .
1111100102 1201   .5   160 . .   .    .    .   .
1111100102 1206    1   140 . .   .    .    .   .
1111100102 1305    .     . . .   1  210    .   .
1111100102 1402    .     . . .   .    . 1500 225
1111100102 1501    4   160 . .   .    .    .   .
1111100102 1502    3   180 . .   .    .    .   .
1111100102 1503    2   160 . .   .    .    .   .
1111100102 1505    1    60 . .   .    .    .   .
1111100102 1508    1    40 . .   .    .    .   .
1111100102 1602  1.5    60 . .   .    .    .   .
1111100102 1608  100    20 . .   .    .    .   .
1111100102 1701    1    66 . .   .    .    .   .
1111100102 1802    1    75 . .   .    .    .   .
1111100102 2000    .  3440 . .   .  500    .   .
1111100102 2001    .  2950 . .   .    .    .   .
1111100102 2002    .   490 . .   .  500    .   .
1111100102 2101   30  1200 . .   .    .    .   .
end
label values itc itc
label def itc 1000 "total of 1001 & 1002 and 1003", modify
label def itc 1001 "total of itc1101to 1310", modify
label def itc 1002 "total of itc1401to 1706", modify
label def itc 1101 "milk(fresh&boiled)", modify
label def itc 1102 "lassi ( buttermilk)", modify
label def itc 1104 "powered milk(for adults and children)", modify
label def itc 1107 "curd\yogurt", modify
label def itc 1201 "beef", modify
label def itc 1204 "eggs", modify
label def itc 1206 "fish(fresh,frozen,dried)", modify
label def itc 1305 "grapes", modify
label def itc 1309 "other fresh fruits (pomegranates, apricot, jamons, lemon, peer, peach, plum, papaya etc.)", modify
label def itc 1402 "other (almond, walnut pine nuts, pistachio, peanuts, aniseed, cashew, coconut, sesame seeds, etc. )", modify
label def itc 1501 "potato", modify
label def itc 1502 "onion", modify
label def itc 1503 "tomato", modify
label def itc 1505 "karala,ladyfinger,brinjal,cucumber", modify
label def itc 1508 "peas,moongra", modify
label def itc 1602 "salt(iodised)", modify
label def itc 1608 "salan masalah/other spices (licorice root, cumin seeds, black pepper,cloves, mixed condiments)", modify
label def itc 1701 "sugar(desi or milled)", modify
label def itc 1802 "squashes & syrups(not medicated)", modify
label def itc 1902 "readymade meals,tea,ice-cream,drinks etc", modify
label def itc 2000 "total of 2001& 2002", modify
label def itc 2001 "total of itc2101 to2506", modify
label def itc 2002 "total of itc2601to 3003", modify
label def itc 2101 "wheat & wheat flour", modify
label def itc 2102 "rice& rice flour", modify
label def itc 2103 "maize,barley,jawar and miller(whole &flour)", modify
label def itc 2202 "dal chana", modify
label def itc 2206 "other(arhaar,chick,peas,sunflower,soyabean)", modify
label def itc 2302 "vegetable ghee", modify
label def itc 2401 "tea (blak,green,loose &packed)", modify
label def itc 2501 "biscuits(sweets&saltish)", modify
label def itc 2701 "firewood", modify
label def itc 2708 "electricity", modify
label def itc 2709 "match box, candles, mantle etc.", modify
label def itc 2710 "beggasses, agricultural wastes for fuel purposes (cotton sticks,sawdust, shrubs,", modify
label def itc 2801 "bath/toilet soap", modify
label def itc 2803 "hair oil & creams, hair tonic & colour, facial cream & powder etc.", modify
label def itc 2804 "toothpaste & powder, brush, miswak", modify
label def itc 2805 "cosmetics such as nail polish,perfumes,lipsticks colognes,lo", modify
label def itc 2901 "hair cutting & dressing etc for men(include shaving material", modify
label def itc 3001 "laundry soap, bleaching and other laundry articles, washing powder, dishwashing articles etc.", modify
label def itc 3002 "household cleaning items like cleaners,dusters,sponges wipes", modify
label def itc 4000 "total of itc4101 to 4408", modify
label def itc 4304 "otravelling by road (bus, taxi, rickshaw etc.)", modify
label def itc 4404 "easy load mobile card, mobile repairing charges.", modify
label def itc 4406 "pocket money to children", modify
label def itc 4407 "expenses on maintenance of pets, poultry and fish (curing) – (for domestic use only)", modify
label def itc 4408 "other expenditures not elsewhere classified", modify
label def itc 5000 "total of itc 5001 & 5002", modify
label def itc 5001 "total of itc 5101 to 5503", modify
label def itc 5002 "total of itc 5601 to 5904", modify
label def itc 5101 "woolen cloth (suits, trousers, coats, etc.), cotton cloth ( shirts, shalwar, etc.), mixed(nylon etc.),dupatta", modify
label def itc 5102 "wool for sweaters, socks, shawls, gloves etc.", modify
label def itc 5103 "new readnew ready-made & second hand garments/under garments (for males, females & children), sweaters, and  sari etc.", modify
label def itc 5104 "burka, chadar, ajrak etc", modify
label def itc 5105 "charges ,clothing supplies(threads, needles, pins, buttons, zipper, and hangers etc.)", modify
label def itc 5106 "tailoring, embroidery, alterations charges", modify
label def itc 5201 "footwear made of leather, synthetic or any other material (all types new or second hand)", modify
label def itc 5202 "repair charges of footwear, polishes, shoe shining and cleaning brushes etc.", modify
label def itc 5302 "imitation and plastic jewellery & ornaments ( bangles, neckl", modify
label def itc 5404 "owner occupied accommodation(market value)", modify
label def itc 5406 "minor repairs/maintenance & redecoration/addition/alteration", modify
label def itc 5501 "crockery & cutlery for daily use", modify
label def itc 5503 "other household effects (bulbs, tubes, switches, battery cel", modify
label def itc 5602 "medical fees paid to (doctors, specialists, hakeem / midwives) outside hospital, including medicine etc.", modify
label def itc 5603 "hospitalization charges, including fee for doctor / hakeem etc and laboratory tests, x-ray charges", modify
label def itc 5604 "dental care, teeth cleaning, extraction, charges, eye glasses and all others expenditure not mention elsewhere", modify
label def itc 5801 "school/college fees and private tuition fees", modify
label def itc 5802 "books and exercise note books / copies, stationary etc other", modify
label def itc 5803 "uniform(trouser shirt/ pent, shirt/ belt/shoes etc", modify
label def itc 5805 "other educational expenses (bags, academies fee etc.)", modify
label def itc 5902 "expenditure on food and soft drinks on religious occasions (births/deaths/marriages) and other such functions etc. (do not include the expenditure incurred on dowry)", modify
label def itc 6000 "total of itc 6101 to 6505", modify
label def itc 6102 "readymade pillow covers, bed sheets, blankets, curtains, mosquito nets etc.", modify
label def itc 6103 "purchase of cloth (for pillow covers, bed sheets quilts etc.) & purchase of cotton (for quilts, pillows, etc.)", modify
label def itc 6104 "stitching charges on household clothing thread and telering expenses.", modify
label def itc 6203 "stainless steel, aluminum and copper utensils", modify
label def itc 6305 "floor coverings ( hand / machine made ), carpets, rugs etc", modify
Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input double hhcode float weights byte hhsize
1111100101  741.6786  7
1111100102  741.6786  3
1111100103  741.6786  4
1111100104  741.6786  1
1111100105  741.6786  8
1111100106  741.6786  8
1111100107  741.6786 13
1111100108  741.6786  4
1111100109  741.6786  8
1111100111  741.6786  8
1111100112  741.6786  2
1111100113  741.6786  6
1111100114  741.6786  3
1111100115  741.6786  2
1111100116  741.6786  8
1111100201  576.5144  6
1111100202  576.5144  5
1111100203  576.5144  6
1111100204  576.5144 10
1111100205  576.5144  5
1111100206  576.5144  5
1111100207  576.5144 10
1111100209  576.5144  5
1111100210  576.5144  9
1111100211  576.5144  6
1111100212  576.5144  8
1111100213  576.5144 10
1111100214  576.5144  5
1111100215  576.5144  4
1111100216  576.5144  4
1111200101  371.8397  1
1111200102  371.8397 12
1111200103  371.8397  6
1111200104  371.8397  5
1111200106  371.8397 10
1111200107  371.8397  2
1111200108  371.8397  7
1111200109  371.8397  5
1111200110  371.8397 11
1111200111  371.8397 10
1111200112  371.8397  7
1112100101  660.1129  7
1112100102  660.1129  8
1112100103  660.1129 13
1112100104  660.1129  7
1112100105  660.1129 10
1112100106  660.1129  2
1112100107  660.1129  8
1112100108  660.1129  8
1112100109  660.1129  8
1112100110  660.1129 10
1112100111  660.1129  5
1112100113  660.1129  7
1112100114  660.1129  7
1112100115  660.1129  8
1112100116  660.1129  6
1113100101  808.2628  5
1113100102  808.2628  2
1113100103  808.2628  4
1113100104  808.2628  4
1113100105  808.2628  7
1113100106  808.2628  8
1113100107  808.2628  7
1113100109  808.2628  2
1113100111  808.2628  2
1113100112  808.2628  4
1113100113  808.2628  5
1113100114  808.2628  8
1113100115  808.2628  4
1114100101 1188.7627  5
1114100102 1188.7627  4
1114100103 1188.7627  4
1114100104 1188.7627  4
1114100105 1188.7627  4
1114100106 1188.7627  6
1114100107 1188.7627 11
1114100108 1188.7627  5
1114100109 1188.7627  5
1114100110 1188.7627  1
1114100112 1188.7627  6
1114100114 1188.7627  7
1114100115 1188.7627  6
1114100116 1188.7627  4
1121100101  6083.979  7
1121100102  6083.979  6
1121100103  6083.979 11
1121100106  6083.979  3
1121100107  6083.979  6
1121100108  6083.979  6
1121100110  6083.979  9
1121100111  6083.979  9
1121100112  6083.979  5
1121100113  6083.979  8
1121100114  6083.979  9
1121100116  6083.979  4
1121100201 3574.6726  6
1121100202 3574.6726  8
1121100203 3574.6726  6
1121100204 3574.6726  7
1121100205 3574.6726  7
end