# 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