Extracting household information collected as part of the personal interviews

Household information should be collected as part of the household level of the interview (not repeated by persons).
Sometimes the household attributes are acquired as part of the individual interview with condition that this section (e.g. on the material of the walls, roof, etc) is asked of only the person who is the household head. This results in a data structure where individual records have values only for the heads, but other household members have all missings in those questions.

The data can be reoptimized at export. The questions pertaining to the households should be extracted from individual level file and added to the household level file.

We do this by writing a script in Stata, SPSS, or other statistical package taking as input the Survey Solutions original exported files and producing the transformed files. In this process do not overwrite, modify or otherwise alter the original files exported from Survey Solutions. Protect them from accidental modification by setting the read only flag and retainig the backup copies. Save temporary files to a scratch folder, save resulting files to a final folder.

The following is the overview of the necessary steps:

  1. open the person-level file

  2. keep only the records corresponding to household heads. Use exactly the same condition that was employed in the Designer (e.g. reltohead==1), not any assumption about the data (such as person__id==1).

  3. in the filtered file keep only the variables corresponding to household attributes and additionally the interview__id variable.

  4. save this reduced file as a temp file

  5. open the household level file (the main file of the survey).

  6. merge with the temporary file saved in step #4 based on the interview__id variable (if your system requires the data to be sorted before merging, sort the files in steps 4 (before saving) and 5 (after opening)).
    In Stata use the command merge.
    https://www.stata.com/manuals15/dmerge.pdf
    In SPSS use the command ADD VARIABLES.
    https://www.ibm.com/support/knowledgecenter/en/SSLVMB_23.0.0/spss/base/idh_addv_main.html

  7. save the combined file as the final households file.

  8. open the original person-level file, drop the variables corresponding to the household attributes, save as the final person-level file.

In the dataset reorganized in this way the household attributes will be stored as part of the household-level data file, and the individual-level data files will only contain individual-level data.

Here are the examples of the input data and the implementation of the above in Stata:
Households file (Stata data)
Individuals file (Stata data)
Example implementation (Stata do-file/script).