Aggregating HH total days of work and Merging it to Individual- level Data

[jesita ajani]

Helo I would like to aggregate individual level data of working_days from working_days1 - working_days9 then collapse it per HHID and wave. Then, I would like to merge it back to the individual level data. I tried using this command but it keeps having an error on generating the totdays. I also used rowtotal but it does not work.

preserve
drop if participation_confirm == 0
gen totdays = days_worked_1 + days_worked_2 + days_worked_3 + days_worked_4 + days_worked_5 + days_worked_6 + days_worked_7 + days_worked_8 + days_worked_9
collapse (sum) totdays, by(hhid wave)
tempfile `dayswork', replace
restore

merge 1:1 hhid wave using `dayswork'
drop if _merge == 2
drop _merge

Later on I would also like to loop it for different variables like earned_income and in-kind money (again, from 1-9). How can I do this too?
Thanks so much~

[Ken Chui]

My guess is that it needs to be saved first, and the assignment of dayswork should not be quoted:

preserve
drop if participation_confirm == 0
gen totdays = days_worked_1 + days_worked_2 + days_worked_3 + days_worked_4 + days_worked_5 + days_worked_6 + days_worked_7 + days_worked_8 + days_worked_9
collapse (sum) totdays, by(hhid wave)
tempfile dayswork
save `dayswork'
restore

Additionally, using + to create the sum is a bad move. If any one of the missing the total sum will be missing. I know you rowtotal didn't work, but you didn't show that code so I can help correct it. But do try this:

preserve
drop if participation_confirm == 0
egen totdays = rowtotal(days_worked_1 days_worked_2 days_worked_3 days_worked_4 days_worked_5 days_worked_6 days_worked_7 days_worked_8 days_worked_9)
* Also possible if there are only these 9 variables starting with "days_worked_":
* egen totdays = rowtotal(days_worked_*)
collapse (sum) totdays, by(hhid wave)
tempfile dayswork
save `dayswork'
restore

[jesita ajani]

Thank you so much, it works. If I want to visualize this on a graph over treatment and wave. What would be the best way and the command? I used a graph bar totdays, over(treatment) over(wave) but is this informative enough in providing the dynamics across treatment groups and waves?

This is the command:
graph bar totdays, over(treatment) over(wave) asyvars bargap(0) ///
ytitle("Total Days Working"), text(-0.5 0 "Wave", placement(s)) ///
legend(label(1 "Control") label(2 "Stable") label(3 "Predictable") label(4 "Unpredictable")) ///
title("Total Days Working")

*save graph
graph export "$figure\totdays.pdf", replace