View Issue Details

IDProjectCategoryView StatusLast Update
16939Bug reportsImport/Exportpublic2021-03-25 11:06
ReporterDeusdeorum Assigned Togabrieljenik  
Status closedResolutionfixed 
Product Version4.3.32 
Summary16939: exporting list question with 'other' to SPSS does not read all data
DescriptionIf you have a survey with a "List (Radio)" question with the other option enabled and someone answer the "Other" option without typing something in the other box you wont get the data in your SPSS file if you use numerical answer codes.
Steps To Reproducei) survey with list radio question, answer codes 1, ..., n and "other" option enabled.
ii) take the survey and answer the other option without typing something in the box.
iii) export syntax and data file to SPSS, execute the syntax file to read the data.

You will now end up with an empty answer for the list question.
Additional InformationThe empty answer is because SPSS Syntax reads the questions like this:

 V9 F1
 V10 A6.

Where the V9 is the list question and V10 is the "other" option, F is numeric col in SPSS, and A is a string col.
When you read the data you will read "-" from the data file into SPSS but this will never get read because "-" isn't a numeric.

Potential fixes?

a) recode "-" on export to a integer which is not used in the answer codes.
b) if "other" option is enabled move it to the other answer options so that users can add the code themselves.
TagsNo tags attached.
Complete LimeSurvey version number (& build)LS DEMO SITE / Linux 5.4.0-58-generic #64-Ubuntu SMP Wed Dec 9 08:16:25 UTC 2020 x86_64
I will donate to the project if issue is resolvedNo
Database & DB-VersionLS DEMO SITE / 5.5.5-10.3.25-MariaDB-0ubuntu0.20.04.1
Server OS (if known)
Webserver software & version (if known)
PHP VersionLS DEMO SITE / 7.4.3


related to 17152 new exporting list question with 'other' to SPSS does not read all data 
related to 17202 resolvedgabrieljenik SPSS Export issues in version 4.4.13 




2021-01-29 13:48

developer   ~61804

Yes, that's a rather serious error (also in 3.25.9) and solving this bug should be top priority!

In old versions, standard response codes were alpha-numeric ("A1", "A2", ...) and the variable format was string in SPSS. However, numeric variable make much more sense for further analyses. So this one could be solved by providing a unique (!) numeric code for the other option.
Deusdeorum's suggestion b) sounds like the best solution to me. But that might be time-consuming.

Anyway, no matter how, this bug should be fixed as soon as possible!


2021-02-03 10:42

reporter   ~61877

I wouldn't say it's a crash but if people doesn't know about it there will be a lot of results from surveys that aint valid. It's weird that LS, which collects data for analysis, is using alpha-numeric in the first place, it's the standard when you create a question, but the standard users will most likely be pissed when they try to calculate a mean when they are done with collecting data (OFF TOPIC).

Your suggestion about a unique numeric code for other option seems to be the way to go for a quick fix.


2021-02-03 11:21

manager   ~61879

Last edited: 2021-02-03 11:24

View 2 revisions

I see, so solution a is the best one :) - good for @gabrieljenik


2021-02-10 22:45

manager   ~62139

Last edited: 2021-02-10 22:45

View 2 revisions

HI All,

If I understand correctly, the solution is to set an arbitrary number to V9 if the "other" option is chosen.
Which number? How the user will know that number?
That will be confusing and will also mess with stats!

Sorry, I don't agree with the solution.

Please find below some comments:
1) V9 format is set to numeric if all answers option codes are numeric.
   If not is set to string.
   No action to be taken here.

2) V9 contents sometimes is "-".
   That's because of a typo on the code.
   I gues initially the code intended to replace "-oth-" with "".
   By the typo, the outcame is the field is filled with the latest "-".

  Action suggested: Add a new parameter (optional) to stripTagsFull() as to also replace "-oth-" to ""

3) V10 format is set to string.
   Action: I would change the code as to set it as string or numeric depending on the "Numbers only for 'Other" question attribute.

Side comment:
The previous actions will lead to:
- if only looking at V9 in SPSS
- not differenciate between an empty answer and a responses where "other" was chosen

To mitigate that, an alternative is:
- always export V9 as string if it allows "other" options.
- have the v9 field to return a special code if the "other" option is selected.
- That code could be fixed or set on a new question attribute

I would just stick to the initial actions.
The alternative could make the SPSS file not to be usefull as V9 would have a string format.



2021-02-11 08:21

reporter   ~62141

I think what @f_funke was suggesting was more like reserving a number for the 'other' option, so that it will always be 998 or something and the user can't pick that number - atleast that's what i was thinking :)
1) still need the user to 'fix' the numeric part after.
2) then we will end up with what we already have (almost). But worse in excel export because then the "V9" will always be NULL. as it is for now you can have "-" in V9 and V10 being NULL (which means that user picked the other option but didn't write anything in the text input).

I am not sure what the solution will be but anything's better than living the black hole as it is for now.


2021-02-11 09:09

developer   ~62146

> That's because of a typo on the code.

Clearly … (sorry)
I don't think need a new param : just remove …


2021-02-11 13:02

developer   ~62158

The closer you look at it, the more complicate is gets. This is mainly because the other option is not displayed together with in simple answer options but kind of hidden in the general question settings. As this cannot be changed easily, one could consider a workaround in SPSS:
- if "other" is selected, the variable is saved as string (even if only numeric values have been entered)
- other option has a special character that other response options don't have (e.g., sticking with "-")
- in SPSS the RECODE command followed by ALTER TYPE could be used to turn a string variable with numeric values only into a numeric variable. For example with the following syntax:

* start SPSS syntax .

RECODE my_var ("-" = "999999") (ELSE = COPY).

ALTER TYPE my_var (F2.0).

VALUES LABELS my_var /* this could become a bit tricky */ .

* end SPSS syntax .

In my eyes, the best solution in the long run would be to get rid of the other option as general setting and to add for each answer option a checkbox if a text field should be added (same should also be done to mark certain responses - like "don't know", "no answer", and "undecided" - as explicit item nonresponse). That would be far more convenient and would also allow more options for response scales.


2021-02-11 16:30

manager   ~62177

OK. Look forward to a confirmation on what is the fix to be done.

The special could should be, to be consistent, "-oth- ".
Keep in mind "-" was just an acidental outcome tah looks OK, but not intended.
Should we keep using "-"? I think "-oth-" is a better fit.


2021-02-16 12:16

developer   ~62282

I just checked in LS 3 and 4. Codes for answer options consist of five characters/numbers max. So we could just ...
... save other option as "-oth-" (as it was)
... in SPSS syntax: use RECODE to turn "-oth-" to a six digit number
... in SPSS syntax: use ALTER TYPE to make it a numerical variable
... in SPSS syntax: use ADD VALUE LABEL to add the label "other" (or the label chosen in the options) to the new 6-digit code

However, I have to check what SPSS does with existing labels after using ALTER TYPE. I'll try to check that tomorrow.


2021-02-24 17:56

administrator   ~62502

@f_funke: Can you check and please let us know. Your idea sounds best to me so far


2021-02-25 11:05

developer   ~62507

Last edited: 2021-02-25 12:09

View 3 revisions

I just checked with SPSS. The following additional code should work to turn a sting variable with other option into a numerical variable:

<SPSS syntax>

ALTER TYPE my_var (A6).

RECODE my_var ("-oth-" = "666666").

ADD VALUE LABELS my_var 666666 "other".

ALTER TYPE my_var (F6.0).

</SPSS syntax>

That syntax does the following with the initial string variable:
1. changes variable length to six characters
2. turns text "-oth-" to "666666"
3. adds the value label "other"
4. transforms string variable into numerical variable

One problem with the syntax above:
If the LimeSurvey question does not contain the other option, the syntax would result in a superfluous label for 666666. This should be avoided in any case because this could lead to a wrong interpretation of the data. @c_schmitz: Is it possible to use certain syntax lines only if the other option has been enabled in a question?


2021-02-25 11:12

developer   ~62508

> RECODE my_var ("-oth-" = 666666).

Maybe best to keep it then ?
Before my issue on typo ( `-oth-` to `-`) : it was deleted, seems didn't exist in SPPS.

If we don't recode : we allow SPSS admin to use it ?


2021-02-25 11:29

reporter   ~62509

RECODE my_var ("-oth-" = 666666).
That should be equal to a string and not a int so:
RECODE my_var ("-oth-" = "666666").


2021-02-25 12:10

developer   ~62510

Thanks, @Deusdeorum ... my bad! I had to retype the syntax as SPSS runs on a different machine. I just corrected it in my initial post to avoid confusion.


2021-02-25 12:13

developer   ~62511

Last edited: 2021-02-25 12:14

View 2 revisions

I'd really prefer to have numeric variables in SPSS, no strings. Numbers saved as strings can a real pain for SPSS users.

At some point in time, I would like to revise the import syntax more substantially. For exampling adding for information (metadata) about the survey would be really nice.


2021-03-02 21:47

manager   ~62625


Please, let's follow this in LS4.
If works as expected, will patch LS3 as well.


2021-03-11 18:07

manager   ~63316

Fix committed to master branch:


2021-03-11 18:07

administrator   ~63317

Merged to master. Please pull and test.


2021-03-15 15:37

administrator   ~63377

Fixed in Release 4.4.13+210315

Related Changesets

LimeSurvey: master abebe9bd

2021-03-11 18:07:00


Committer: GitHub Details Diff
Fixed issue 16939: exporting list question with 'other' to SPSS does not read all data (#1791) Affected Issues
mod - application/controllers/admin/export.php Diff File
mod - application/helpers/export_helper.php Diff File

Issue History

Date Modified Username Field Change
2021-01-04 09:27 Deusdeorum New Issue
2021-01-29 13:48 f_funke Note Added: 61804
2021-01-29 13:49 f_funke Severity minor => crash
2021-01-29 13:49 f_funke Status new => confirmed
2021-02-03 10:42 Deusdeorum Note Added: 61877
2021-02-03 11:21 cdorin Note Added: 61879
2021-02-03 11:24 cdorin Note Edited: 61879 View Revisions
2021-02-08 17:37 ollehar Severity crash => block
2021-02-08 17:38 ollehar Priority none => high
2021-02-08 17:38 ollehar Severity block => partial_block
2021-02-10 22:45 gabrieljenik Note Added: 62139
2021-02-10 22:45 gabrieljenik Note Edited: 62139 View Revisions
2021-02-11 08:21 Deusdeorum Note Added: 62141
2021-02-11 09:09 DenisChenu Note Added: 62146
2021-02-11 13:02 f_funke Note Added: 62158
2021-02-11 16:30 gabrieljenik Note Added: 62177
2021-02-16 12:16 f_funke Note Added: 62282
2021-02-24 17:56 c_schmitz Note Added: 62502
2021-02-25 11:05 f_funke Note Added: 62507
2021-02-25 11:05 f_funke Note Edited: 62507 View Revisions
2021-02-25 11:12 DenisChenu Note Added: 62508
2021-02-25 11:29 Deusdeorum Note Added: 62509
2021-02-25 12:09 f_funke Note Edited: 62507 View Revisions
2021-02-25 12:10 f_funke Note Added: 62510
2021-02-25 12:13 f_funke Note Added: 62511
2021-02-25 12:14 f_funke Note Edited: 62511 View Revisions
2021-03-02 21:47 gabrieljenik Note Added: 62625
2021-03-02 21:48 gabrieljenik Issue cloned: 17152
2021-03-02 21:48 gabrieljenik Relationship added related to 17152
2021-03-11 18:07 gabrieljenik Changeset attached => LimeSurvey master abebe9bd
2021-03-11 18:07 gabrieljenik Note Added: 63316
2021-03-11 18:07 gabrieljenik Assigned To => gabrieljenik
2021-03-11 18:07 gabrieljenik Resolution open => fixed
2021-03-11 18:07 ollehar Note Added: 63317
2021-03-15 15:37 lime_release_bot Note Added: 63377
2021-03-15 15:37 lime_release_bot Status confirmed => closed
2021-03-25 11:06 ollehar Relationship added related to 17202