Sam Winstanley’s Blog

Market Research Technology News and Views

Sam Winstanley’s Blog header image 2

Excel Multiple Response Data in SPSS Reporter

August 13th, 2008 · 5 Comments


After my last blog post about using auto-categorization in Desktop Reporter, I was asked the question:

“How do I analyze multiple response data which is coming from Excel”

Here’s a walkthrough of 1 way that you can handle it.

The Data

I started with data which is actually a profile extraction of the Museum database that I exported to Microsoft Excel which looks as follows (in Excel):








General knowledge and education

National Museum of Science




Not answered

National Museum of Science




Interest/satisfy curiosity

Museum of Design,National Art Gallery


Museums_Visited is a multiple response which is comma separated.

Splitting out the categories.

Before we can really work on the data in Reporter we need to make a couple of tweaks to it in excel.

Inside Excel – Select the Museums_Visited Column then click on the “Text to Columns” button:

This starts the conversion Wizard.

The end result of this is a brand new column or 2, actually 5 new columns in my case, there is one column for each response to Museums_Visited and the most responses anybody made to that question is 5. (This is what SPSS Base users will commonly call a “Category Set”).

Now the data is in this shape we can take the rest to Reporter.

Opening up in Reporter

We open up the Excel file in Reporter and it recognises each column to be Text, Text is not great for doing tabulations! it needs some work…

To make the Text variables usable for tabulations we need to categorize them, and we do that using the new Categorize feature, as follows:

Note I have unchecked the “Other” option and very importantly Not asked (NULL) is checked FOR the first category only this point is very important for a reason I will mention later, the second variable should be categorized with these options:

Note that for this one the User Missing Category is checked.

And repeat this for all of the 5 new variables until we have along with the other categorical variables in the data until we have (note that there are new variables and these have new icons):

The final step is to make a new variable that combines our Museums_1-5 into a multiple response.

Hit the select button which opens the Expression builder… We want our new variable to add together all the individual responses to the Museums_1-6 variables to make one new multiple response variable.

Click okay, Desktop Reporter does some work in the background and this screen now has responses in it.

Click Save and close to save this new variable into the data.

Then Export the data to a new format as follows:

Then open the newly exported data.

Note here that I am opening an MDD file after exporting a DDF file.

Drum roll Please

There we have excel multi-response data tabulated in Desktop Reporter.

Some final notes

  1. Why did you set up the first Museum variable to use NULL and the others to not use Null.
    I knew I was going to make the multiple response variable which had the expression Museums_1+Museums_2+Museums_3 etc.. I also knew that Museums_1 was the real base of the variable, (e.g. people with a blank to Museums_1 actually did not answer that question). The Dimensions DataModel has special ways of dealing with the NULL value (this is called the ANSI SQL 92 way to handle nulls). As a result 1+NULL=NULL…. In our case if Museums_1 had a value and Musuems_5 did not then the end result of Museums_1+….+Museums_5 would always be null which is definitely not what was expected.
  2. Why so many steps.. it must be possible to make it simpler.
    All of these steps can be automated if you are comfortable with mrStudio and particularly with TOM scripting. In the end Excel isn’t a great data format, especially when you talk about multiple response data and I think Reporter is doing a good job of getting around some of those weaknesses. This is a slightly specialized case because in the end if the data you have in excel is really simple like this you can achieve a good result just using excel itself.
  3. Why the export to DDF, why not just analyse in the Excel format.
    There’s definitely a performance reason why it’s a good idea, but I also did find some problems with making tables before I did the export. I suspect there were ways to workaround these but I’d already decided I was going to export the data to something that could natively hold multiple response data.

Tags: Dimensions · Market Research Industry · Reporting · Tabulation · Tech

5 responses so far ↓

  • 1 Suvash // Aug 14, 2008 at 10:26 am

    Thanks a lot for this posting! It was immensely helpful!

  • 2 Suvash // Sep 4, 2008 at 2:44 pm

    Hey Sam,

    I was playing with SPSS multiple response table. After creating a multiple response sets, I saved the file and exported to Desktop reporter. After that, it was easier to crosstab the Multiple response question in Desktop reporter. It also saves lot of time.

    But now I have a different question for you..a tough one for me.! How to create a grip table for rating/scaling questions (rating 1-5) when different statements for a same variable are in different column. What DR is doing right now is treating different statements, let’s say 1a, 1b, 1c, 1d (all in different column) as seperate questions. What I am looking is trying merge all these statement into a single table so that if we want top box (ratings 4, 5), we just have a single table..Any ideas would be immensely useful for novice DR user like me and others trying to figure this out!

  • 3 sam // Oct 3, 2008 at 3:51 am

    At the moment in Reporter you can’t build grid variables inside the user interface, its the kind of thing that seems likely to come along in the future. To build a grid variable like this you would have to do some programming against the Meta Data for your project.

  • 4 bhavesh // Mar 23, 2009 at 1:22 am

    hi, I have a one query.

    in the above you have written “All of these steps can be automated if you are comfortable with mrStudio and particularly with TOM scripting. In the end Excel isn’t a great data format”

    Please if you could suggest how we can do this in .mrs. I am using the sav fiule directly and not converting the same to mdd.

  • 5 Lucia // Jul 14, 2010 at 7:09 pm

    Thank you! Lots helpful :)

Leave a Comment