Just arriving? Check out Part 1 and Part 2.
Link lookup list values to each variable
11. Import the question.xls and lookupEntry.xls files into a NEW MS Access database.
12. Create a relationship between the tables using the LookupID field in the lookupEntry table and the popupOptions field in the questions table.
13. Use the query wizard to create a query that contains the following fields:
Table: question –> form
Table: question –> internalName (variable name)
Table: lookupEntry –> LookupID (lookup list name)
Table: lookupEntry –> LookupDisplay (list item)
Table: lookupEntry –> LookupValue (item value)
14. Your Pendragon database will include every form you ever created. Therefore, you will need to limit the query to the forms you used for the survey. View the properties for each survey form in Pendragon and record the form ID. Open your query in design view and enter the form ID values in the “Criteria Row” of the “form” column (e.g., 432597750 Or 432009264).
15. Export the query as an Excel file and delete the “form” column.
Create STATA code to generate new variables and fill with numeric codes
16. Insert a column (B) after internalName and name it “duplicate”.
17. Create a 6th column with double quotes (“). Fill down.
18. Create a 7th column called “generate.” Copy the following code into cell G2. This code will tell STATA to create a new (lowercase) variable with an underscore suffix for each variable name. Fill down.
=CONCATENATE(“gen “,LOWER(A2),”_ = .”)
19. Create a 8th column called “replace.” Copy the following code into cell H2. This code will tell STATA to replace the blank contents of the variables you just created with the correct numeric value from your original variables. Fill down.
=CONCATENATE(“replace “,LOWER(A2),”_”,” = “,E2,” if “,LOWER(A2),” == “,F2,D2,F2)
20. Copy the generate column into a STATA do file and run.
21. Copy the replace column into a STATA do file and run.
Create STATA code to assign value labels
22. Manually create the label variable codes for each lookup list. I could not figure out an easy method to automate this process given the structure of the Pendragon table. Suggestions are welcome.
e.g., label define YesNoSometimes 1 “Yes” 0 “No” 3 “Sometimes”
23. Copy these codes into your do file and run.
24. Create an additional column after “replace” called “label values.” Copy the following code into cell H2. This code will tell STATA to assign the labels defined in step 22 to your variables.
=CONCATENATE(“label values “,LOWER(A2),”_”,” “,LOWER(C2),”_”)
- Make a copy of your worksheet
- Paste this formula in the header row of the “duplicate” column you created in step 16: =IF(COUNTIF($A$1:A1,A1)>1,”Dup”,”")
- Fill down
- Data –> Filter –> Autofilter
- Filter your new column to show only blanks
26. Copy the unique label values column to your do file and run.
That’s it. 26 easy steps! Pendragon, please make this easier. Got a better way? Leave some ideas in the comments.
