before we start

For the readers who are interested in getting OpenClinica-data into SPSS: we have a whole page about that! You can find it here.

checkboxes and multiselects in SPSS

Yet another fine feature of OpenClinica is that you can use check-boxes in your CRF. For example: you can ask which days of the week the participant did not take the medication.

fig. 1: check-box in a CRF

You will probably be aware of the fact that the answers to a check-box item are stored in the database as a comma-separated list of values. Therefore both OpenClinica and SPSS will treat the check-box-item as a variable of type ST, character-string or Alpha-numeric.

fig. 2: the items in SPSS

And when we look at the data in SPSS, we see that the days of the week that were checked are part of this comma-separated list.

fig. 3: the data in SPSS

analyse this

But for our analysis we might be interested in the total score for Thursday. That means we want to scrutinize each answer and if it contains a 4, then we want to mark this answer as including Thursday. To do this we first define in SPSS a new variable, called NUMERIC DOW_E1_C1_4: we choose this name, because the variable name of the check-box-item is DOW_E1_C1 and the value for Thursday is 4.
As a next step we set this new variable to zero with COMPUTE DOW_E1_C1_4=0.
Then we use the CHAR.INDEX-command: this gives us the start-position of one or more characters in a string. If this is greater than 0, we know that 4 is in our answer. So the command we use is:
IF (CHAR.INDEX(DOW_E1_C1, '4')>0) DOW_E1_C1_4=1.

We're almost there: all we need to add are the variable and value labels for our new variable:
VARIABLE LABELS
DOW_E1_C1_4 "On which days did you not take the medication? - Tick all that apply: Thursday"
VALUE LABELS
DOW_E1_C1_4
0 "No"
1 "Yes"

And once we know how to do this for one day, we know it for all, so our SPPS-syntax will look something like this:
NUMERIC DOW_E1_C1_1 DOW_E1_C1_2 DOW_E1_C1_3 DOW_E1_C1_4 DOW_E1_C1_5 DOW_E1_C1_6 DOW_E1_C1_7 (F1).

COMPUTE DOW_E1_C1_1=0.
COMPUTE DOW_E1_C1_2=0.
COMPUTE DOW_E1_C1_3=0.
COMPUTE DOW_E1_C1_4=0.
COMPUTE DOW_E1_C1_5=0.
COMPUTE DOW_E1_C1_6=0.
COMPUTE DOW_E1_C1_7=0.
EXECUTE.

IF (CHAR.INDEX(DOW_E1_C1, '1')>0) DOW_E1_C1_1=1.
IF (CHAR.INDEX(DOW_E1_C1, '2')>0) DOW_E1_C1_2=1.
IF (CHAR.INDEX(DOW_E1_C1, '3')>0) DOW_E1_C1_3=1.
IF (CHAR.INDEX(DOW_E1_C1, '4')>0) DOW_E1_C1_4=1.
IF (CHAR.INDEX(DOW_E1_C1, '5')>0) DOW_E1_C1_5=1.
IF (CHAR.INDEX(DOW_E1_C1, '6')>0) DOW_E1_C1_6=1.
IF (CHAR.INDEX(DOW_E1_C1, '7')>0) DOW_E1_C1_7=1.
EXECUTE.
VALUE LABELS
DOW_E1_C1_1 DOW_E1_C1_2 DOW_E1_C1_3 DOW_E1_C1_4 DOW_E1_C1_5 DOW_E1_C1_6 DOW_E1_C1_7
0 "No"
1 "Yes"
/
.
EXECUTE.

VARIABLE LABELS
DOW_E1_C1_1 "On which days did you not take the medication? - Tick all that apply: Monday" /
DOW_E1_C1_2 "On which days did you not take the medication? - Tick all that apply: Tuesday" /
DOW_E1_C1_3 "On which days did you not take the medication? - Tick all that apply: Wednesday" /
DOW_E1_C1_4 "On which days did you not take the medication? - Tick all that apply: Thursday" /
DOW_E1_C1_5 "On which days did you not take the medication? - Tick all that apply: Friday" /
DOW_E1_C1_6 "On which days did you not take the medication? - Tick all that apply: Saturday" /
DOW_E1_C1_7 "On which days did you not take the medication? - Tick all that apply: Sunday" /
.
EXECUTE.

and this always works?

Unfortunately this construction can only be applied for check-box-items with unique options, so if your 1 is an option and 11 is also an option, then we have a problem. The reason is of course that when 11 is in the answer and we check for 1 this syntax will put have a positive result. The solution for this is to check for each option between comma's. "But then 1 will never score" you might think and you are right. We correct this by adding an extra comma to the start and to the end of our answer. Now we can check for ,1, if we look for 1 and ,11, will not give a false positive.

The command to use is CONCAT and the syntax is:
IF (CHAR.INDEX(Concat(',', LTrim(RTrim(DOW_E1_C1)), ','), ',1,')>0) DOW_E1_C1_1=1.

fig. 4: after running the syntax

great, let's start typing

Yes, now we can start typing the SPSS-syntax. But wouldn't it be nice if we had a utility to generate the code? Exactly. We wrote one in MsAccess/VBA and you can find it here.

It opens with a form and the form has four tabs: one to generate the SPSS-syntax and three to collect the necessary information about the check-box-item. This can be done in three different ways:
1 - by using REST, which is available in OpenClinica starting from version 3.2
2 - by using web-services: you should have these installed; take a look at this page
3 - by using an ODM-file, either from OpenClinca directly or from the extract of a dataset

If you are running OpenClinica 3.2, then REST is by far the easiest method. Open tblSOAPParameters and scroll down to item sysURLREST and change that to match your OpenClinica-installation. Then go to tab metadata from rest and fill in your username and password and the OID of your Study. You can find this by clicking in OpenClinica on the link of your Study-name in the upper-left corner of the screen and in figure 5 the Study OID is S_FFF. Next click on get metadata.

fig. 5: the Study OID

fig. 6: getting metadata using REST

Alternatively, you can use web-services. Open tblSOAPParameters and go to record sysURL and change that to match your situation. Use tab metadata from web-service and fill in username and password and click get studies. After the list of available studies has been refrehed, choose your study and click get metadata

fig. 7: getting metadata using web-services

The third method is by either downloading the ODM file with the metadata or extracting any dataset in ODM-format. Look again at figure 5: there's the link Download the study metadata here: use it and save the file somewhere on your PC. Browse to it and yes, click get metadata.

fig. 8: getting metadata from file

Now go to the first tab, generate spps-syntax and choose the check-box-item. You must also fill in the extension and you can find that in your SPSS-file. Click on generate SPSS-syntax and you're done.

fig. 9: generating the syntax

Other how-to-pages can be found here.

this page was last reviewed April 2014