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

[William Buchanan <william@williambuchanan.net>]

Hi Allan,

One thing that I think is helpful to this discussion is the assumption that the survey designer selected the appropriate answer options for each of the survey items.  When I've had to work with people's data from survey monkey in the past one of the more painstaking tasks has been collapsing responses to a single item that are spread over several columns.  This usually happens when the question format is set up in a way that the end user can select multiple responses (which is definitely appropriate in some situations).  More often than not, this seems to be something overlooked by folks when creating the questionnaire and the resulting item (usually some type of demographic/background variable) gets garbled up significantly.  

However, if you're using the current version of Stata you can use the -import excel- command to get the data into Stata and then use the tools available in Stata to clean things up.  

HTH,
Billy



On Jun 29, 2012, at 8:49 AM, Allan Reese (Cefas) wrote:

> 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/


*
*   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/