Study Data Extracts

The Study Data Extract job allows data managers to extract and download study execution data from EDC Tools. You can choose to download your data as a CSV, SAS, or XPT file before running the job. Note that the encoding for CSVs and SAS is UTF-8, so it is recommended that you set your encoding to UTF-8 when viewing the export. This page shows SDE columns for the current release. To view columns from previous versions, see SDE Versioning.

Repeating Item Groups: Vault adds a row of data for each repeating Item Group. Rows are distinguished by their Item Group Sequence.

Timezones: Vault uses the UTC timezone, Site’s timezone, and user’s timezone across all datasets, which are displayed as separate columns for clinical datetime items. System datetime columns (like LASTRUN) use the UTC timezone. Note that the Job History tab will display the file created in the user’s timezone while files on the user’s computer may display in UTC timezone, depending on the user’s Operating System. Learn more about timezones here.

Clinical Datasets

Each clinical dataset, and each Form (Adverse Events, Concomitant Medications, etc.), contains a set of Key Columns and a set of Clinical Data columns, which are detailed below.

Key Columns

Key Columns contain data to help identify which Form each row is referring to (ex: which Subject, Site, Cycle, or Event that particular form is located in).

The list of Key Columns is as follows:

This format shows up to 3 items on the form, up to the maximum character limit.

Clinical Data

After the Key Columns, the system will insert Clinical Data columns, with each column representing an item on the Form.

Clinical Data Item Headers use the item names or external ID defined in Studio. Some items can be associated with more than one column, depending on their data type:

1. Codelists are split into two columns:
   • ITEMNAME: this column contains the codelist code
   • ITEMNAME_D: this column contains the codelist decode or label
2. Units are split into four columns:
   • ITEMNAME: this column contains the value entered in the item
   • ITEMNAME_U: this column contains the unit entered in the item
   • ITEMNAME_TRANS: this column contains the “translated” item value, which is the value that was entered that has been converted to the standard unit
   • ITEMNAME_TRANSU: this column contains the standard unit
3. Datetimes are split into three columns:
   • ITEMNAME: this column contains the datetime value entered (site’s timezone)
   • ITEMNAME_UTC: this column contains the datetime value converted to UTC
   • ITEMNAME_USER: this column contains the datetime value converted to the timezone of the user running the Study Data Extract

Unknown parts in dates and datetimes are represented by UN in ISO format:

• For a date: 2021-UN-UN
• For a datetime: 2021-UN-TUN:UN:UN.UNKZ

You can choose from the following Clinical Data options:

Casebook Versions

All casebook versions are displayed in Study Data Extracts, so items that were deleted from the schedule in subsequent casebook versions will still appear in Clinical Datasets. This inclusion guarantees that all data is extracted, even if certain subjects were kept on a previous casebook version.

The Study Data Extract job first adds columns for all items that are present in Casebook Version 1 before adding columns for any items that may have been added in subsequent casebook versions to create the Study Data Extract file.

Lab Columns

Each form configured with Local Labs contains certain columns that are dynamically added after the LASTRUN column for lab items. Refer to the table below for a complete list of columns.

*This field refers to the Lab ID.

The LBDTC, LBLOC, and LBAGE columns are part of the Lab Header that is generated for each lab form. Each column related to a Lab Analyte with a “Unit” type has additional columns for the Unit (appended with _U), the Translated Value (appended with _TRANS), and the Translated Unit (appended with _TRANSU). For example, the Lab Result Unit for Sodium would display in the column header as LBORRES_Sodium_U.

Coding Data

If a form is set up for coding, meaning that a coding configuration has been defined in Studio, coding-related columns will be added after clinical data columns. If there are multiple verbatims set up in a form, the system will add a set of coding columns for each verbatim configured. If the same item in different item groups is configured for coding, the system will add “itemgroup.item” to each column header to indicate which set of columns belongs to which verbatim. Note that you need the View Code permission to view coding columns in the SDE.

List of coding columns for MedDRA:

List of coding columns for WHODrug:

List of coding columns for JDrug:

Definitions File

The Study Data Extract ZIP file contains a definition file, which is a listing of all columns across all datasets included in the extract.

List of columns included in the Definitions file:

System Datasets

Along with clinical datasets, the Study Data Extract file contains several system datasets that contain operational data and metrics.

SYS_SITE

The SYS_SITE dataset lists all sites for the selected study.

List of SYS_SITE columns:

SYS_SUB

The SYS_SUB dataset lists all subjects for the selected study.

List of SYS_SUB columns:

SYS_EVT

The SYS_EVT dataset lists all events as well as event dates and status for the selected study.

List of SYS_EVT columns:

SYS_FORM

The SYS_FORM dataset lists all forms for the selected study, including forms Intentionally Left Blank as well as review statuses, freeze and lock statuses, and additional metrics.

List of SYS_FORM columns:

SYS_Q

The SYS_Q dataset lists all queries for the selected study, along with some query metrics. Only the first query message is listed in this dataset. The full list of query messages (first message, answers, etc.) is included in the SYS_QT dataset.

If you have the Show Queries in the User Language setting configured, queries will display in the user’s language, not the vault language.

List of SYS_Q columns:

SYS_QT

The SYS_QT dataset lists all query messages for the selected study.

You can link the content of this file with the SYS_Q dataset using the QUERYID column. There can be multiple SYS_QT records matching one SYS_Q record (i.e. a query in SYS_Q can have multiple query messages in SYS_QT).

If you have the Show Queries in the User Language setting configured, queries will display in the user’s language, not the vault language.

List of SYS_QT columns:

SYS_ILB

The SYS_ILB dataset lists all items that have been marked Intentionally Left Blank in the selected study.

List of SYS_ILB columns:

SYS_LINKS

The SYS_LINKS dataset lists all forms that belong to a form link in the selected study. Forms that are linked together will share the same link ID, which will display in the GUID column. The form unique identifier (FGUID) can then be used to identify a specific form and access its data.

List of SYS_LINKS columns:

SYS_ASM

The SYS_ASM dataset lists the following information about assessments, if assessments are configured for a Study.

SYS_ASMR

The SYS_ASMR dataset lists questions and answers for all assessments in a Study, if assessments are configured.

SYS_PD

The SYS_PD dataset lists the following Protocol Deviations information, if Protocol Deviations is enabled.

SYS_RAND

The SYS_RAND dataset lists the following Randomization information, if Randomization is enabled.

*These columns are only available for unmasked studies with Randomization enabled and users with the View Unmasked Data and View Randomization Enrollment permissions. To include these columns, check the Include Randomization Treatment box in the New Job dialog. Note that you can’t download SDE files from users with the View Randomization Enrollment permission if the Include Randomization Treatment checkbox is not visible to you in the SDE job dialog.

SYS_LABRANGES

The SYS_LABRANGES dataset lists the following Labs information, if Labs is enabled.

SYS_LABLOC

The SYS_LABLOC dataset lists the following Labs information, if Labs is enabled.

SYS_ANALYTES

The SYS_ANALYTES dataset lists the following Labs analyte information, if Labs is enabled.

SYS_SAFC

The SYS_SAFC dataset lists the following Safety Case information for users with studies configured with a Safety Configuration.

SYS_SAFM

The SYS_SAFM dataset lists the following Safety Message information for users with studies configured with a Safety Configuration.

Form, Event, & Query Status

Below are statuses and their descriptions for forms, events, and queries.

Form Status

Event Status

Query Status

SAS Format

If you select SAS as the Export File Format when scheduling your Study Data Extract job, the generated ZIP file contains two additional folders:

• SAS: Contains sas7bdat files for each dataset
• XPT: Contains XPT files for each dataset

Key columns use fixed conversions to either num or char SAS types, whereas clinical data columns are converted to a specific SAS type based on the item data type in EDC.

SAS validations and formatting:

• Column names are limited to 32 characters. The system will truncate as necessary to append special suffixes like _TRANSU or _D.
• SAS labels are limited to 255 characters. Labels that exceed 250 characters are truncated and have an ellipsis (…) added to the label.
• ZIP file names are limited to 200 characters.
• If a dataset starts with a number, it will be prefixed with an underscore (_).
• If a clinical dataset has the same name as one of the system datasets, it will be appended with “_X” (X being a number that can be incremented).
• If a column header starts with a number, it will be prefixed with an underscore (_).
• If a value is entered with carriage returns (new line), these characters are replaced with spaces.
• If the same column header is present multiple times (same item used in multiple item groups in the same form), the system appends “_X” (X being a number that can be incremented) to the column header. For column headers that already have a suffix (e.g. _TRANS), the “_X” suffix will be appended after the special suffix is added (_TRANS_2).
• Double byte numbers will be replaced by their single byte equivalent

You will see an additional column in your Study Definitions file for the SAS column that will show any column header manipulations that were made due to SAS format conventions.

To view column names (instead of SAS labels) in the SAS viewer, right click and select Display Labels.

Note that SDE versions 22R1 and later will automatically compress SAS zip files.

We use SAS version v9.4. SAS behavior will dynamically set the XPT version based on the input of the data, which can either be V5 (V5 transport), V8 (V8 extended transport), or V9 (V9 extended transport).

CSV Format

The following naming conventions apply to extracts that are exported as a CSV file as of SDE Version 23R1 and later:

• Column names are limited to 32 characters. The system will truncate as necessary to append special suffixes like _TRANSU or _D.
• CSV labels are limited to 255 characters. Labels that exceed 250 characters are truncated and have an ellipsis (…) added to the label.
• ZIP file names are limited to 200 characters.
• If a dataset starts with a number, it will be prefixed with an underscore (_).
• If a clinical dataset has the same name as one of the system datasets, it will be appended with “_X” (X being a number that can be incremented).
• If a column header starts with a number, it will be prefixed with an underscore (_).
• If a value is entered with carriage returns (new line), these characters are replaced with spaces.
• If the same column header is present multiple times (same item used in multiple item groups in the same form), the system appends “_X” (X being a number that can be incremented) to the column header. For column headers that already have a suffix (e.g. _TRANS), the “_X” suffix will be appended after the special suffix is added (_TRANS_2).
• Double byte numbers will be replaced by their single byte equivalent

FTP Connection

To configure an FTP connection, see Connecting to an FTP Server from EDC Tools.

Custom Objects

You can include custom objects in your exports by adding records to the Study Data Extract Custom Object Config object in Business Administration where you must specify the Name of the record, custom object name (objectname__c), and the Study that you’re going to run the SDE for.

Once you’ve configured a custom object and run the SDE job, you will receive a file named after your custom object label (CUSTOM_), which will have records for each custom object you configured.

The order of the columns in the custom object file follows the Page Layout configuration, with the exception of the GUID of the object always appearing at the beginning of the file.

Duplicate column names are appended with an _2, _3, etc. to prevent column name conflicts.

Note that custom object records must be unique within a study.

List of Custom Objects columns:

Running the Study Data Extract Job

To run the Study Data Extract job:

1. Navigate to your Study in Tools > EDC Tools.
2. Click Jobs.
3. Click + New Job.

4. Select Study Data Extract for Type.

5. Optional: Select Include Restricted Data to include data from restricted Forms in your extract.
6. Optional: Select Include Randomization Treatment to include Randomization treatment information in the SYS_RAND dataset.
7. Select which version of the SDE you’d like to run. Each job version determines which files are included in the extract and which version of the CSVs must be used. Note that the default version selected will be the latest version that is supported in the current release. See SDE Versioning for details.
8. Optional: Make your selections for Clinical Data Options to determine how Vault handles clinical data formatting.
9. Select a Boolean Formatting option. Note that boolean formatting applies to Clinical and System Datasets.

10. Optional: Select which datasets to export. All System Datasets and Clinical Datasets will be selected by default. To specify which datasets to export, uncheck the Export All checkbox.

11. Optional: Enter a ZIP File Name. This name is the filename that Vault will use for the generated ZIP file.
12. Select one or more Export File Type.
13. Choose your Item Display Label. The Item Display Label determines whether the label in the study definitions file and the SAS label will use the Item Label or Item Short Label as defined in Studio.
14. Optional (only 22R3 Version): Select Include Study Design to include a Definitions folder containing study design information in your extract. See below for more information on the Include Study Design option.
15. Optional: Select an External Connection to receive the output file. Otherwise, Vault will send you a link to download the output when the job finishes.
16. Optional: Configure a Frequency to run the job on a recurring basis. Learn more about scheduled jobs.
17. Click Run Now.

Include Study Design (Version 22R3 and later)

The following files will be included in the Definitions folder when the Include Study Design option is checked:

• Export Options: Includes all export options saved in the job record
• Casebook Definitions: Includes all casebook versions
• Casebook Schedule: Includes all casebook versions
• Casebook Definition Summary: Includes information about the relationships between the Event Group, Event, Form, Item Group, and Items
• Event Definitions: Includes information about Events and Event Groups
• Event Group Repeat Overrides: Includes information about repeating Event Groups, if applicable
• Form Definitions: Includes information about Forms
• Item Definitions: Includes information about Items and Item Groups
• Codelists: Includes Codelist information
• Unit Codelists: Includes Unit Codelist information
• Protocol Deviations: Includes information about Protocol Deviations, if applicable
• Protocol Deviations Relationships: Includes information about Protocol Deviations relationships, if applicable
• Medical Coding Items: Shows all medical coding items configured for a coding form, if applicable
• Medical Coding Related Items: Shows optionally configured related items on a coding form, if applicable
• Medical Coding Copy Indication from Links: Shows Form Links configured for Indication-related Item Types, if applicable

The contents of the Casebook Schedule, Casebook Definition Summary, Item Definitions, and Form Definitions files will appear in the order configured in Studio.

Send Data to CDB (Version 23R2 and later)

In the 23R2 SDE version, users with CDB enabled can send SDE datasets to CDB, where the datasets will be handled like third-party data. The Study Label is used in the SDE, while the Study Name is used in CDB. Both values must be the same for the data to transfer successfully to CDB.

You can run two (2) Send to CDB Study Data Extract jobs outside regular SDE job governance limits.

To send SDE data to CDB:

1. Start a new Study Data Extract job in EDC Tools > Job Schedule.
2. Choose 23R2 under SDE Version.

3. Select the Send to CDB Workbench checkbox under the Send Data to CDB subsection and select a Manifest file. See example file below.

4. Follow steps 5-17 in the Running the Study Data Extract section above. Note that you must select specific datasets to export (you can’t select the Export All Clinical Datasets option – we recommend not exporting clinical datasets) and you can only export your file as a CSV when sending to CDB.

Example Manifest File

See below for an example Manifest file. Note that this is an example file, so the custom object file name will be your custom object label.

{
  "study": "Deetoza_DEV1",
  "source": "SDE Data",
  "blinded": false,
  "data": [
    {
      "filename": "SYS_ASM.csv",
      "study": "STUDYID",
      "site": "SITENUM",
      "subject": "SUBJID",
       "rowid": {
        "groupid": ["GUID"]
      },
      "event": {
        "default": "baseline_visit"
      },
      "items": {
        "COUNTRY": {
          "type": "text"
        },
        "ASMNAME": {
          "type": "text"
        },
        "ASMLABEL": {
          "type": "text"
        },
        "ASMEID": {
          "type": "text"
        },
        "ASMTYPE": {
          "type": "text"
        },
        "REASSESSMENT": {
          "type": "text"
        },
        "SOURCEF": {
          "type": "text"
        },
         "SOURCEFDEF": {
          "type": "text"
        },
        "STATUS": {
          "type": "text"
        },
        "CREATEDT": {
          "type": "datetime",
          "format": "yyyy-MM-dd'T'HH:mmZ"
        },
        "CREATEDB": {
          "type": "text"
        },
        "COMPDT": {
          "type": "datetime",
          "format": "yyyy-MM-dd'T'HH:mmZ"
        },
        "NSUBMITS": {
          "type": "integer"
        },
        "LASTMODDT": {
          "type": "datetime",
          "format": "yyyy-MM-dd'T'HH:mmZ"
        },
        "LASTMODB": {
          "type": "text"
        },
        "GUID": {
          "type": "text"
        },
        "FGUID": {
          "type": "text"
        },
        "LASTRUN": {
          "type": "datetime",
          "format": "yyyy-MM-dd'T'HH:mmZ"
        }
      }
    },
    {
      "filename": "SYS_ASMR.csv",
      "study": "STUDYID",
      "site": "SITENUM",
      "subject": "SUBJID",
      "rowid": {
        "groupid": ["QUESTEXTDEF", "QUESANS", "AGUID"]
      },
      "event": {
        "default": "baseline_visit"
      },
      "items": { 
        "ASMNAME": {
          "type": "text"
        },
        "ASMLABEL": {
          "type": "text"
        },
        "ASMEID": {
          "type": "text"
        },
        "QUESTEXT": {
          "type": "text"
        },
        "QUESTEXTDEF": {
          "type": "text"
        },
        "QUESANS": {
          "type": "text"
        },
        "CREATEDT": {
          "type": "datetime",
          "format": "yyyy-MM-dd'T'HH:mmZ"
        },
        "CREATEDB": {
          "type": "text"
        },
        "LASTMODDT": {
          "type": "datetime",
          "format": "yyyy-MM-dd'T'HH:mmZ"
        },
        "LASTMODB": {
          "type": "text"
        },
        "AGUID": {
          "type": "text"
        },
        "LASTRUN": {
          "type": "datetime",
          "format": "yyyy-MM-dd'T'HH:mmZ"
        }
      }
    },
    {
      "filename": "CUSTOM_TEST_CUSTOM_OBJECT.csv",
      "study": "STUDY",
      "site": "SITE",
      "subject": "SUBJECT",
      "event": {
        "default": "baseline_visit"
      },
      "rowid": {
        "groupid": ["GUID"]
      },
      "items": { 
        "GUID": {
          "type": "text"
        },
        "NAME": {
          "type": "text"
        },
        "STATUS": {
          "type": "text"
        },
        "CREATEDB": {
          "type": "text"
        },
        "CREATED": {
          "type": "datetime",
          "format": "yyyy-MM-dd'T'HH:mm:ssZ"
        },
        "MODIFIEDB": {
          "type": "text"
        },
        "DLASTMOD": {
          "type": "datetime",
          "format": "yyyy-MM-dd'T'HH:mm:ssZ"
        },
         "STUDY_COUNTRY": {
          "type": "text"
        }
      }
    }
  ]
}

Refer to Importing 3rd Party Data for more information about manifest files in CDB.

Troubleshooting

If extract jobs such as the Study Data Extract, Study Listings, or Detail PDF fail after removing or moving data from the study schedule in your DEV or TST environments, try deleting the casebook data before retrying the jobs. Deleting data such as Event Groups, Forms, Item Groups, and Items from the study schedule while it is still in the design process is not supported by EDC and will cause exports to fail.

To avoid extract jobs failing, keep an eye on the following changes that may cause extract job failure:

Changes in the Study Schedule:

• Removal of Event Groups, Events, Forms, Item groups, and Items

Changes in Business Admin

• Modification of object records relating to Events, Forms, Item Groups, and Items