Tag Archives: Oracle Clinical

Count the number of discrepancies per procedure – OracleClinical (OC)

Let’s now write a quick program to count the number of discrepancies per procedure in OC/OCRDC:

Remember to comment /**/ or ***comment here*; what the program does. It is a good clinical practice to document everything so anyone can read your program and make the necessary updates, if necessary.

proc sql;
connect to oracle(path=ocpath);
create table discr as select * from connection to oracle
(Select  p.name, pd.test_order_sn detail, count(pd.test_order_sn) count, p.procedure_id procid
from discrepancy_management dm,
procedures p,
procedure_details pd
where dm.clinical_study_id=9999
and dm.procedure_id = p.procedure_id
and dm.procedure_detail_id=pd.procedure_detail_id
and p.PROCEDURE_VER_SN=pd.PROCEDURE_DETAIL_PROC_VER_SN
and dm.PROCEDURE_VER_SN=p.PROCEDURE_VER_SN
and dm.de_sub_TYPE_CODE=’MULTIVARIATE’
group by p.name, pd.test_order_sn, p.procedure_id
order by count(p.name)desc
);
/*document your code*/
proc sql;
connect to oracle(path=ocpath);
create table name as select * from connection to oracle
(select distinct p.procedure_id procid, p.name, pd.TEST_ORDER_SN detail
from  procedures p,
procedure_details pd
where p.clinical_study_id= 9999 *replace with your studyid;
and p.procedure_status_code !=’R’
and p.procedure_id=pd.procedure_id
order by procid
);
quit;

/* merge # of discrepancies with name */
proc sort data=discr;
by procid;
run;

proc sort data=name;
by procid;
run;

data discname;
merge discr (in=d) name (in=n);
by procid;
if n;
run;

proc sort data=discname ;
by descending count ;
run;

/* print out  */
proc print data=discname label;
var name numdisc percent numdcf;
label numdisc = ‘Number of discrepancies’
numdcf = ‘Number of DCFs’;
title “Number of discrepancies per Procedure”;
title2 “RA eClnica”;
run;

You could also export the report to Excel xls and have your DM / data manager review it.

Good luck and let me know if it was helpful.

To hire me for services, you may contact me via Contact Me OR Join me on LinkedIn

Advertisements

Open Source versus Commercial Systems

Types of EDC Systems: Commercial and Open Source

EDC systems can either be standalone databases on a desktop computer/server supporting a single site or they can be Web based with the ability to support multisite studies [1, 3, 27]. Based on the business model utilized and the licensing-distribution method followed, they can be broadly categorized as commercial and open-source EDC systems. Commercial EDC applications are usually developed by a for-profit company or developer group. They charge for user licenses with or without annual support contracts while the source code is not published. Some examples include Oracle® Clinical (Oracle, USA) [31], Clinsys® (Jubilant Organosys, USA) [6], InForm™ (Phase forward, USA) [22] DATATRAK Electronic Data Capture (DATATRAK, USA) [8]. On the other hand, free and open-source software are applications developed by a single or group of developers, often as a voluntary effort. The application and its source code are published online and users can download them without any cost. Some examples include DADOS P (Research on Research group, Duke University, USA) [7] OpenClinica® (Akaza Research, USA) [30], Redcap (Vanderbilt University, USA) [34] and TrialDB (Yale University, USA) [43] .

opensource_commercial

Commercial EDC Systems

Commercial EDC systems can either be purchased as a software package or through a license with periodic support either included in the package or charged separately. In some cases, users have to pay a one-time license fee while in other cases users can renew their license periodically. Troubleshooting and guidance are usually provided under support plans while bugs in the application are rectified and released under regular updates.

Commercial EDC applications are usually easy to use in light of the quality of documentation and customer support extended to users. Well-designed interfaces are responsible for their user-friendly design. Multiple levels of user access, security, adherence to industry and regulatory standards, support for the design of eCRF’s, data entry, and management are features common to many commercial EDC applications. Some of them may also be able to generate reports, for example, DATATRAK One™ (DATATRAK international Inc, USA) [8], Entrypoint Plus® (Phoenix Software International Inc, USA) [10], and CliniProteus (Roskamp Bioinformatics Core, USA) [5]. Although standalone EDC systems are prevalent, they are increasingly being offered as a part of a complete clinical trial management system (CTMS). For example Oracle Clinical and InForm are offered as a part of a larger CTMS.

Despite the benefits, commercial EDC systems are expensive [13] and frequently non-customizable. They have been considered inadequate in context to the needs of healthcare stakeholders (clinicians, administrators, and patients) [46]. Given the variety of clinical practices and research methods used, commercial EDC systems may not fit into the workflow at each clinical/research site, thus having implications on their effective and efficient use. Many of them do not support interoperable data standards, i.e. it may not be possible to merge data exported out of one EDC with data exported from another EDC system or research/clinical application. As a result data from multiple studies or sources cannot be merged and utilized for answering research questions. Since the source code is not released, users must depend on the developer group or vendor for customization and support-related requirements. This limitation is frequently referred to as “vendor lock-in,” which makes further development and maintenance of commercial EDC an expensive effort.

We reviewed the web to identify some of the prominent and popular commercial EDC systems. They include Oracle® Clinical, InForm™ and Rave® (Table 1). Among themselves they hold a larger share of the EDC market [3]. Other examples include DATATRAK and eTrials EDC (Merge Healthcare) [8, 11].

Oracle® Clinical [31] is a commercial EDC system for conduction of clinical studies and trials. It is a core component of an integrated eClinical research solution that integrates adverse event report reporting, thesaurus management, trial management and remote data capture features in a single application.

InForm™ [22] global trial management system delivers a variety of features essential for the effective and streamlined implementation of clinical studies and trials. It sports an impressive study setup page, scalability and has separate EDC and CDMS databases. Other features include an intuitive interface, streamlined workflow, reporting and analysis tools that help the study team to work more efficiently. It can also be seamlessly integrated with randomization, trial design, and medical coding modules.

The Rave® (Medidata solutions) [36] platform is another industry leader in EDC systems. It has an impressive study design tool that nullifies the need for programming skills. It offers a single, flexible and scalable platform that captures, manages and reports clinical research data. It also undertakes a balanced approach between ease of use, features and functionalities. It has the flexibility to interface with legacy systems, adheres to CDISC clinical data standards and sports a plugin for modifying the interface and functionality.

DATATRAK Electronic Data Capture has considerable market presence [8]. Its features range from patient data management, electronic forms, supports queries, alerts and visit scheduling and generation of custom reports. It is also equipped with custom checklists and workviews, configurable tools for data cleaning, real time statistical support and an integrated medical coding package.

eTrials EDC [11] is a web based application that collects, manages and analyzes clinical trial information in real time. It is a user-friendly yet robust application with an in-built workflow. It can generate reports and can easily integrate with data from other applications.

Despite being feature rich, scalable, secure and compliant to industry standards, these EDC systems are prohibitively expensive [13], thus limiting their use by individual investigators and users from developing countries that do not having adequate funding but are interested in research participation.

Open-source EDC Systems

Open and freely available source code released under open distribution licenses like general public license [14] is the defining feature in this category of EDC systems. Open source code generates a large community of users and developers that interact, modify, and enrich the source code over a period of time and report bugs and solutions, thereby enhancing the quality, features, and value of the EDC system. In order to sustain an open source license, developers usually charge for customization requests. The same is applicable to troubleshooting and support, which are usually delivered through a yearly contract or support plan. Thus, by providing free access to the source code and annulling restrictions on use, modification, and distribution, open-source EDC systems form an attractive alternative for users.

The availability of inexpensive (or free) open source EDC applications for individual physicians/researchers, departments, and institutes has the potential to improve clinical and research activities and enhance academic standards by reaching a wider audience. Since further development/customization and ownership costs are lower, institutional administrators can modify and adapt open-source EDC systems to suit their environment and workflow, thus ensuring the success of EDC implementation. User-friendly and simple interfaces, adherence to industry standard security protocols, customizability, interoperability, and low maintenance costs are some of the major benefits of open-source EDC systems. Additionally, the presence of user support groups and communities ensures continuing support.

Although open-source EDC systems have a unique mix of features, their adoption in healthcare organizations has been slow. Software cost and maintenance are not the only features that influence decision makers. For an institution/organization-wide implementation, decision makers usually prefer and opt for EDC systems that are easy to deploy, manage, and support. Not all open-source EDC systems qualify for the same. In addition, dependence on the developer community for support and updates may cripple the organization if the community stops being productive. It is possible to address this issue by hiring programmers who could work on further development and maintenance, But locating and training relevant workforce is a challenge as in many cases the background technology has a steep learning curve.

DADOS Prospective, OpenClinica® and Redcap are examples of open source EDC systems. DADOS Prospective is a Web-based application developed by the research on research group (RoR) [37] to support data collection activities among researchers, research groups, and research networks [7]. It enables users to replicate any case report form into an eCRF, collect data in single/multisite studies, and extract data in an interoperable format. It is compliant with Chapter 11, Title 21, Code of Federal Regulations [4] and Health Privacy and Accountability Act (HIPAA) guidelines for EDC. It can be used to streamline and support individual/departmental/institutional databases, registries, and single/multisite clinical/nonclinical studies and clinical at a low cost [28].

OpenClinica® [30] is an open source application that has both EDC and data management capabilities. As an EDC system, it not only facilitates collection, validation, and annotation of clinical data but also has features that allow study audits, reporting and data extraction.

Research electronic data capture—‘Redcap’ is an open source metadata driven application designed for the clinical and translational research target audience by Vanderbilt. Its features include: a streamlined process for building a database, an intuitive and secure data collection interface that supports data validation and automated data export in multiple formats. It also supports other advanced features such as branch logic and file upload. It is freely available to its consortium partners with a modest personnel investment of < 0.5 FTE that covers training and support activities [16, 34].

Source:http://www.ncbi.nlm.nih.gov/pmc/articles/PMC3049639/

Articles from Clinical Orthopaedics and Related ResearcH
1Duke-NUS Graduate Medical School, Singapore, Singapore
2Research on Research Group, Duke University Medical Center, Durham, NC USA
3Department of Surgery, Duke University Medical Center, Box 3094, Durham, NC 27710 USA
4National Neuroscience Institute, Singapore, Singapore
Ricardo Pietrobon, Email: rpietro@duke.edu.
FAIR USE-
“Copyright Disclaimer Under Section 107 of the Copyright Act 1976, allowance is made for “fair use” for purposes such as criticism, comment, news reporting, teaching, scholarship, and research. Fair use is a use permitted by copyright statute that might otherwise be infringing. Non-profit, educational or personal use tips the balance in favor of fair use.”

Your comments and questions are valued and encouraged.
Anayansi Gamboa has an extensive background in clinical data management as well as experience with different EDC systems including Oracle InForm, InForm Architect, Central Designer, CIS, Clintrial, Medidata Rave, Central Coding, OpenClinica, Open Source and Oracle Clinical.

4 Programming Languages You Should Learn Right Now (eClinical Speaking)

I am a strong believer that learning a new language makes you better at the others, but I am not a “learn to code” advocate since a foreign language (I know 3 languages and currently learning my 4th and I have a “to learn” language including Italian and Arabic, if I ever find some free time) or even music (I love to play drums) are equally beneficial. But if you want to obtain a job in the pharmaceutical industry, here are the list of programming languages you should learn:

  1. C#:

What it is: A general-purpose, compiled, object-oriented programming language developed by Microsoft as part of its .NET initiative.

Why you should learn it: If you are looking to become a Medidata Custom Function programmer or Oracle InForm EDC Developer then you should.

2. Python:

What it is: An interpreted, dynamically object-oriented, open-source programming language that utilizes automatic memory management.

Why you should learn it: If you are like me always looking to learn new technology, love Google platforms and perhaps want to become a Timaeus Trial Builder, you should learn it. It is used on a lot open-source technologies.

Everyone should learn to code

3. PL/SQL or SQL:

What it is: PL/SQL stands for Procedural Language/SQL.

Why you should learn it: If you are like me additive to databases then Oracle should be your choice. If you want to become an Oracle Clinical programmer or Database administrator, you should learn Oracle PL/SQL.

4- SAS

What it is: SAS stands for “Statistical Analysis System” (software). It is the most powerful and comprehensive statistics software available.

Why you should learn it: SAS skills are in high demand nowadays. If you are able to obtain the SAS Certification and a few years of experience in the Pharmaceutical industry, you will be in good shape. If you are new and looking for training there are several options available from SAS Institute to private vendors such as Clinovo to even learning on your own. I most warn you as it will be difficult to obtain a job without experience. Nevertheless, once you are in, it can only get better.

Remember that your job is not just to code but to solve real problems. Your ability to code covers a lot of range of skills: from critical thinking, problem analysis & solving, logic, etc.

So which one are you going to give a try?

Let me know what is your preference. Happy Programming!

The best thing about a boolean is even if you are wrong, you are only off by a bit.(Anonymous)

Anayansi Gamboa has an extensive background in clinical data management as well as experience with different EDC systems including Oracle InForm, InForm Architect, Central Designer, CIS, Clintrial, Medidata Rave, Central Coding, OpenClinica Open Source and Oracle Clinical.

Disclaimer: The legal entity on this blog is registered as Doing Business As (DBA) – Trade Name – Fictitious Name – Assumed Name as “GAMBOA”.

Source:
SAS Institute
Learn PL/SQL

Data Management Plan in Clinical Trials

 

The preparation of the data management plan (DMP) is a simple, straightforward approach designed to promote and ensure comprehensive project planning.

The data management plan typically contains the following items. They are:

  1. Introduction/Purpose of the document
  2. Scope of application/Definitions
  3. Abbreviations
  4. Who/what/where/when
  5. Project Schedule/Major Project Milestones
  6. Updates of the DMP
  7. Appendix

The objective of this guidelines is to define the general content of the Data Management Plan (DMP) and the procedures for developing and maintaining this document.

The abbreviation section could include all acronyms used within a particular study for further clarification.

e.g. CRF = Case Report Form
TA = Therapeutic Area

The Who/What/Where/When section should describe the objective of the study specific data management plans for ABC study. This section provides detail information about the indications, the number of subjects planned for the study, countries participating in the clinical trial, monitoring guidelines (SDV) or partial SDV, if any CROs or 3rd party are involved in the study (e.g. IVRS, central labs), which database will be used to collect study information (e.g. Clintrial, Oracle Clinical, Medidata Rave or Inform EDC).

The Appendix provides a place to put supporting information, allowing the body of the DMP to be kept concise and at more summary levels. For example, you could document Database Access of team members, Self-evident correction plan, Data Entry plan if using Double-data entry systems or Paper-Based clinical trials systems.

Remember, this is a living document and must be updated throughout the course of the clinical trial.

If problems arise during the life of a project, our first hunch would be that the project was not properly planned.

Reference: Role of Project Management in Clinical Trials
Your comments and questions are valued and encouraged.
Anayansi Gamboa has an extensive background in clinical data management as well as experience with different EDC systems including Oracle InForm, InForm Architect, Central Designer, CIS, Clintrial, Medidata Rave, Central Coding, OpenClinica, Open Source and Oracle Clinical.

To hire me for services, you may contact me via Contact Me OR Join me on LinkedIn

Disclaimer: The legal entity on this blog is registered as Doing Business As (DBA) – Trade Name – Fictitious Name – Assumed Name as “GAMBOA”.

Oracle Clinical (OC) Cheat Sheet

OC Shortcuts

Code Description
Space Bar Check or uncheck Checkbox
CRTL+Q Back up to last screen and/or Exit
F3 Copy data from row above
F6 Insert new record below
F7 Enter into Query mode
F8 Execute Query
F9 List of Values (LOV) for entry
F10 Save
ROUND ROUND(number,precision) – Rounds number to the decimal value determined in the precision parameter
e.g. ROUND(BMI,1)
TRUNC TRUNC(number) – Removes decimal places from number
e.g. TRUNC(WEIGHT)
NVL NVL(string,’value if null’) – Returns specified value if string is null
e.g. NVL(VITALS.VISITDT,’01’)
DECODE DECODE(expression,value1, return1, value2, return2,,,,,, default) – Functions as an inline if statement. If the expression results in value 1, then report return1, and so on. If no values are found function returns the default.e.g. DECODE(VISIT_NO,10,20,30,40,50,88,99)
UPPER/LOWER UPPER(string) / LOWER(string) – Sets string to all uppercase or lowercase
e.g. UPPER(AETERM)
LOWER(AETERM)
LENGTH LENGTH(string) – Returns an integer value of the length of the value of the string
e.g. LENGTH(ENROLL.RANDOMNO) < 6

Source: OC User Manual

Good Documentation Practice (GDP) for the EDC / SAS Developer

When writing programming codes for either validating the software or for validation checks, we often have to write comments to explain why we did something.

Since the FDA regulates computerized systems used in clinical trials under the authority of Title 21 the Code of Federal Regulations Part 11 (21 CFR Part 11) – see my other article about 21 CFR Part 11 here, we need to make sure our codes and programs are documented. As you have heard before, if it is not documented, it never happened. Nevertheless, there is no mandatory regulatory agency mandating to have to do this.

GDP is an expected practice”

So how much documentation is needed? We could get into endless discussions of when we should comment, what we should comment, and how much we should comment. I have had plenty of discussions about comments with people with various opinions on the subject.

Here’s a good documentation practice for a SAS code:

The program header was written to validate Clintrial (Oracle).

  • Program name, version, programmer and purpose.
  • Modifications
  • Risk Assessments

The second section contains information about the

  • quality testing, user testing
  • Macros, global variables and any other code that is reusable.

The document must tell the entire story about your program and must be readable by internal or external staff. Two other important things to remember, your program must be accurate “error free” and each section of your program must be traceable, such as who updated it, what and why.

Most companies have SOPs that requires you to record certain information. But do we understand what it is we are recording? or when it was recorded?

Standardized Documentation is KEY”

Do you have a preference? Tell me about it in the comments!

To hire me for services, you may contact me via Contact Me OR Join me on LinkedIn

Anayansi Gamboa has an extensive background in clinical data management as well as experience with different EDC systems including Oracle InForm, InForm Architect, Central Designer, CIS, Clintrial, Medidata Rave, Central Coding, OpenClinica Open Source and Oracle Clinical.

SDTM Terminology

The current version of the Study Data Tabulation Model Implementation Guide Version 3.1.2 (SDTMIG v3.1.2) answers to CDSH questions to comply with SDTM terminology.

Some key points to remember:

  • You need to define which codelists will be applied to which questions
  • Most data entry systems require a concise list of potential terms per variable/field
  • SDTM terminology codelists are big, e.g. C66786 – COUNTRY which covers all potential COUNTRYs for all clinical trials / site management
  • Controlled terminology gap – we need to develop new terms

So how SDTM terminology become possible?

In order to improve the efficiency of human drug review through required electronic submissions and standardization of electronic drug application data, FDA and industry leaders are working together in this initiative.

Format in EXCEL


SDTM controlled terminology is extracted from the NCit by an automated procedure that creates a report organized into terminology codelists. These codelists correspond to CDISC variables.

To access SDTM Controlled Terminology visit CDISC website and click on Standards & Innovations –>; Terminology

This is the superset of codelists that are used to both collect and submit SDTM data

Excel Format – Column descriptions in the Controlled Terminology (SDTM subset)

Column. Description
Code (column A) Unique numeric code randomly generated by NCI Thesaurus (NCIt) and assigned to individual CDISC controlled terms.
Codelist Code (Column B) Unique numeric code randomly generated by NCI Thesaurus (NCIt) and assigned to the SDTM parent codelist names. This code is repeated for each controlled term (aka permissible value) belonging to a codelist. As of 9/22/2008, this code was dropped for parent codelist entries, where it created confusion.
**NOTE – light blue highlighting is used to identify the beginning of a new SDTM codelist and its applicable term set.
Codelist Extensible (Yes/No) (Column C) Defines if controlled terms may be added to the codelist. New terms may be added to existing codelist values as long as they are not duplicates or synonyms of existing terms. The expectation is that sponsors will use the published controlled terminology as a standard baseline and codelists defined as “extensible” (or “Yes”) may have terms added by the sponsor internally.
Codelist Name (Column D) Contains the descriptive name of the codelist which is also referred to as the codelist label in the SDTM IG. As with the Codelist Code, the Codelist Name is repeated for each controlled term belonging to a codelist.
CDISC Submission Value (Column E) IMPORTANT COLUMN: Currently (as per SDTMIG 3.1.2) this is the specific value expected for submissions. Each value corresponds to a SDTM Codelist Name as indicated by light blue shading.
CDISC Synonym(s) (Column F) This identifies the applicable synonyms for a CDISC Preferred Term in Column F. **NOTE – this is especially important in instances where a Test name or Parameter Test name contains a corresponding Test Code or Parameter Test Code.
CDISC Definition (Column G) This identifies the CDISC definition for a particular term. In many cases an existing NCI definition has been used. The source for a definition is noted in parentheses (e.g. NCI, CDISC glossary, FDA).
NCI Preferred Term (Column H) This identifies the NCI preferred name for a term as identified in NCIt. **NOTE – This column designates the human readable, fully specified preferred term corresponding to the NCI c-code, and is especially helpful for searching NCIt to get the entire concept with links to all instances of the term.

Above example of the spreadsheet for the ‘Route of Administration‘ codelist.

Reference: Clinical Data Interchange Standards Consortium, Inc (CDISC) Representing Controlled Terminology in CDASH

Anayansi Gamboa has an extensive background in clinical data management as well as experience with different EDC systems including Oracle InForm, InForm Architect, Central Designer, CIS, Clintrial, Medidata Rave, Central Coding, OpenClinica Open Source and Oracle Clinical.