View Issue Details
|ID||Project||Category||View Status||Date Submitted||Last Update|
|16939||Bug reports||Import/Export||public||2021-01-04 09:27||2021-03-25 11:06|
|Summary||16939: exporting list question with 'other' to SPSS does not read all data|
|Description||If 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 Reproduce||i) 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 Information||The empty answer is because SPSS Syntax reads the questions like this:|
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.
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.
|Tags||No tags attached.|
|Complete LimeSurvey version number (& build)||LS DEMO SITE / Linux limesurvey-2.limesurvey.org 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 resolved||No|
|Database & DB-Version||LS DEMO SITE / 5.5.5-10.3.25-MariaDB-0ubuntu0.20.04.1|
|Server OS (if known)|
|Webserver software & version (if known)|
|PHP Version||LS DEMO SITE / 7.4.3|
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!
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.
I see, so solution a is the best one :) - good for @gabrieljenik
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.
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.
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.
> That's because of a typo on the code.
Clearly … (sorry)
I don't think need a new param : just remove …
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.
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.
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.
|@f_funke: Can you check and please let us know. Your idea sounds best to me so far|
I just checked with SPSS. The following additional code should work to turn a sting variable with other option into a numerical variable:
ALTER TYPE my_var (A6).
RECODE my_var ("-oth-" = "666666").
ADD VALUE LABELS my_var 666666 "other".
ALTER TYPE my_var (F6.0).
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?
> 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 ?
RECODE my_var ("-oth-" = 666666).
That should be equal to a string and not a int so:
RECODE my_var ("-oth-" = "666666").
|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.|
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.
Please, let's follow this in LS4.
If works as expected, will patch LS3 as well.
|Fix committed to master branch: http://bugs.limesurvey.org/plugin.php?page=Source/view&id=31313|
|Merged to master. Please pull and test.|
|Fixed in Release 4.4.13+210315|
LimeSurvey: master abebe9bd
Committer: GitHub Details Diff
|Fixed issue 16939: exporting list question with 'other' to SPSS does not read all data (#1791)||
|mod - application/controllers/admin/export.php||Diff File|
|mod - application/helpers/export_helper.php||Diff File|
|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|