st: Getting data from SurveyMonkey (via Excel) into Stata

["Allan Reese (Cefas)" <allan.reese@cefas.co.uk>]

Survey Monkey is a web-based tool for doing questionnaires.  There are different levels of subscription.  The free-use level will export you responses in an Excel sheet containing question text in row 1, list of answers in row 2, cases in subsequent rows.
See http://help.surveymonkey.com/app/answers/detail/a_id/273/related/1

I had to import several sheets this week, and thought it worthwhile to share the experience.

The first few variables are generated by SurveyMonkey, then the questions in columns.

I like to keep the original as sheet 1 (and label the tab "survey").

Copy all sheet 1 to sheet 2 (tab "data") and delete rows 1 & 2 (optional, but may save specifying the range).  The data can be copy/pasted from sheet 2 into Stata data editor and generates varnames var1, var2 etc.  If you use the inbuilt Stata feature to import the sheet, it uses the column headers as names (A, B, C, ... AA, etc) as var names. (see below)

Copy rows 1 2 from the survey sheet & "paste transpose" in sheet 3 (tab "labels").  Insert new col A and fill with index numbers - type 1 & 2 then drag down to fill.  In col D or later, generate Stata -var labels- commands using a formula something like ...
      ="label var var" & A1 & "   """ & B1 & " : " & C1  & """"      

Put that in D1 and copy down column.   """" inserts one double quote character, but I didn't find this in Excel help.

Edit text in cols B and C to taste - that's why it's on a copied sheet.  Since these were questions with rubric on the SurveyMonkey form, they probably shorten a lot.  I had to edit in "Q1", "Q2", etc at the starts to help link to the printed questionnaire.
Col D then copied and pasted into Stata DO-file editor, saved as xxxlabels.do

If you do -import- the data values as vars A, B, C, etc, you need to generate these names in the labels sheet.  Excel doesn't make it easy!
Put 1,2,3, etc down column A as before.  Put this formula in E1,    
      =LEFT( ADDRESS(1,A1,2), FIND("$", ADDRESS(1,A1,2))-1 )
 
and copy down column.  Write E1 etc in place of "var"&A1 etc in column D formula.

Comments welcomed, especially if there's a simpler way.  If you pay SurveyMonkey, there is an option to output an SPSS file.

Allan







*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/