Tag Archives: OC

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
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

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

proc sort data=name;
by procid;

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

proc sort data=discname ;
by descending count ;

/* 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”;

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

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
NVL NVL(string,’value if null’) – Returns specified value if string is null
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
LENGTH LENGTH(string) – Returns an integer value of the length of the value of the string

Source: OC User Manual

OpenClinica Data Mart

“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.”

Source: Akaza

Oracle Clinical – How to Use Study Design

Study Design in OC is the process of setting up the protocol for the study. This includes:

  •  creating a record for the study
  • creating patient positions or placeholders
  • creating events or study visits
  • assigning sites or locations where data is collected
  • assigning patient positions to the site

Remember that the required study planning objects, sites and investigators must be created prior to the Design Process being completed.

It is a good idea to review all protocol and study related documentation prior to creating the study to make sure you have all of the necessary information but you can always change the Design elements at any time except for the study name.

Once the Study Design is completed, you can move to the next module: The Study Definition (creating CRFs) and develop Procedures (Edit Checks, derivations).


  •  Records for the new studies are created in the Easy Design module (Design, Studies, Easy Design)
  • Verify that the required Planning Objects exist for the study
  • In the Easy Design form create the study. Enter the study name or number, version and study description/title. Some parameters are optional. Once you click save, the system will prompt you to choose whether the study requires Pass 2 Data Entry.
  • Most Study Design parameters may be changed except fr the Study Name

Study Design Key Terms:

  • Program: Code (name) for the compound being investigated
  • Protocol: Document describing the plan of action for a study
  • Project: Code (name) for the indication under investigation
  • Study: The name for the Clinical Study
  • Organizational Unit: Code (name) for the unit responsible for the study
  • Event: Clinical Planned Event or Visits
  • Region: Code (name) of the location where the study is managed
  • Patient Positions: Identifier for a participant in a study
  • Site: A location where all or part of the study is conducted
  • Investigator: Primary researcher/clinician for the study at a site

Study Design – Events

• Study timeline is used to identify when data is collected or for tracking purposes (missing or overdue DCMs)

• Consists of one or more intervals and one or more events (visits)

• Timeline consists of intervals that are subdivided into events. By default each study is pre-populated with two defaults intervals that can be used in creating events.

• To create intervals, select the study in the Easy Design module and click on Intervals. Intervals are defined by a Phase Name, Short Name, Phase Type, Blind type (single, double, etc) and a minimum and maximum duration. The duration is used to calculate when the interval is expected to take place within the study.

• To create events, select the study in the Easy Design module and click on Events. Create all the events (visits) in which data will be collected during the course of the study. Events are defined by Event Name, Interval, Visit Number (the order o f the event is expected to occur) and minimum and maximum Offsets from the Interval Start.

• Time calculations (event offsets and interval durations) are useful only for descriptive purposes and for determining if expected CRFs are Missing or Overdue.

If this functionality is not required then this information is not useful in the execution of the study.

Study Design – Patient Positions

• Patient Positions are the placeholders for the actual partaker in the study. Each patient for whom data will be collected must have a unique patient position within that study.

• Can be crated in blocks or one-by-one.

• Patients can be of several types: Screening, Normal or Replacement.

For general patients, use NORMAL.

Replacements are used in Randomization.

• To create patient positions, select the study in the Easy Design module; click on Create PP. Create the required patient position for the study by entering starting and ending numbers.

• Duplicates numbers are not allowed within a study.

Study Design – Sites and Investigators

• Sites are the locations where the data is collected and investigators represent the medical researcher at the site responsible for the patients. It can be used in multiple studies.

• Each study requires a minimum of one site assigned to it with an investigator assigned to that site.

• Create Sites in the Sites module. A site is defined by a Code, Name, Phone Number, Address, City, State, Country, and Postal Code. Site code must be unique.

Design ->Investigators and Sites -> Sites

Create Investigators in the Investigators module. An Investigator is defined by a Investigator Code, First Name, Last Name, and Phone Number. Other information is optional. Investigator code must be unique.

Design -> Investigators and Sites ->Investigators

• Assign an Investigator to each site. There can only be one active Investigator assigned to a site at any time. If a second Investigator is assigned to the same Site, the system automatically enters a Termination Date for the current Investigator.

• Assign Patient Positions to the Study Sites. Patients may be optionally enrolled in the study. Enrolling patients can be performed in the Enrollment module.

Tip: The system only requires the enrollment date to consider a patient “Enrolled”, however, the lab range system will not work without the entry of the patient’s birth date and sex.

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”.