Clinical Trial Spreadsheet Excel

Document Sample
Clinical Trial Spreadsheet Excel Powered By Docstoc
					                                                          Paper AD13

    A Simple Solution for Managing the Validation of SAS® Programs That
                      Support Regulatory Submissions
                                    Tony Chang, Amgen Inc. Thousand Oaks, CA
                                    Dana Soloff, Amgen Inc. Thousand Oaks, CA


ABSTRACT
Managing the testing and validation of SAS programs that produce deliverables for regulatory submissions can be a daunting
task. In the pharmaceutical industry, the life cycle for a program can be as short as 15 minutes, as iterative changes are
made, and SAS programs are retested and released under tight time pressures. Consequently, keeping track of the testing
status for all SAS programs for a clinical study is a challenge that many companies face. However, most of the commercially
available tracking software for tracking this information is too burdensome and inefficient to use in such a rapid-paced
production environment.

The PI Manager is a Java application we have developed that easily and automatically tracks the progress of testing and
validation for all SAS programs used for the reporting of a clinical trial. It uses an MS Excel spreadsheet to contain information
about all SAS programs and their validation status. The application provides the following functions to improve the efficiency
and quality of SAS program testing and validation process:
     1. The PI Manager automatically updates the last modification date and time of each SAS program and its validation
          documentation based on system dates and times.
     2. It automatically creates hyperlinks to SAS programs, their outputs, their source data sets, and all testing programs
          and validation documents associated with each SAS program.
     3. It retrieves the overall testing result (Pass/Fail) from individual testing documents and updates the worksheet based
          on this status.
     4. It identifies programs that require retesting due to modifications made since the last testing was done.
     5. It summarizes the overall programming status for the study by providing the total number of programs and the
          number and percent of the programs that have been through testing, that have passed or failed, and that require
          retesting due to modification.

Key Words: SAS Program Validation Process, Java, Excel Spreadsheet

INTRODUCTION
Managing the testing and validation of SAS programs that produce deliverables to regulatory submissions is a considerable
challenge in the pharmaceutical industry. While the validation tasks for SAS software are no different than for any other
software, the timeframe in which those steps must be accomplished in the pharmaceutical industry pose significant challenges
to ensuring that adequate change control processes are in place and followed. The life cycle of a SAS program can be as
short as 15 minutes. Last minute changes can be requested and immediately implemented, the program is re-executed, re-
tested and then the output delivered instantly. To make matters more difficult, changes to requirements can overtake the
testing process, testing may not be complete when requirements change and a new version of the program is needed. During
the final period before a filing, with many programmers working simultaneously on a number of studies, as well as numerous
statisticians or clinicians requesting changes, keeping track of which programs have been changed, and which need retesting,
can be overwhelming.

Most of the commercially available tracking software is designed to accommodate all of these change control issues, but not
the timelines under which programmers in the pharmaceutical industry work. The software is simply too burdensome and
inefficient to use in such a rapid production environment. This leaves many companies manually keeping track of the changes
and testing status for each program. This is nearly impossible to do consistently and ensure quality, especially given the large
number of outputs produced in a single clinical study, much less an entire regulatory submission.

We built a simple, Java-based application called the PI Manager to help automate the management of programming and
testing of SAS programs. It easily and automatically tracks the progress of programming and testing for all SAS programs
used for the reporting of a clinical trial. The PI Manager is designed specifically for the unique environment of pharmaceutical
programming.

PROGRAM INDEX FILE
The PI manager relies on a MS Excel spreadsheet, called the Program Index (PI) file, that provides the SAS program and
output names in a clinical study. The MS Excel spreadsheet collects all programming deliverables for the study. The
programming outputs can be organized into separate sheets based on their types, such as SAS analysis data sets, tables,
listings, and graphs. Table 1 shows an example of a Program Index file containing SAS program information for the
deliverable tables of a clinical study. It includes columns for the following information:
      •    SAS program name
      •    Last modified date of the SAS program
      •    Output file names
      •    Validation document names
      •    The last modified date for the validation documents
      •    The last overall testing result for the SAS program
      •    The overall status of the program.


 SAS             Program       Output File             Validation        Last          Testing     Status
 Program         Last                                  Document          Testing       Results
                 Modified                                                Date
                 Date

 t_demog.sas     1/20/2005     t_14_01_demog.rtf       v_t_demog.xls     1/30/2005     Pass        Complete

 t_disp.sas      1/20/2005     t_14_02_disp.rtf        v_t_disp.xls      1/30/2005     Fail        Program requires modification

 t_base.sas      1/20/2005     t_14_03_base.rtf        v_t_base.xls      1/30/2005                 Testing in progress

 t_ae.sas        1/21/2005     t_14_04_ae.rtf                                                      Programming started

 …               …             …                       …                 …             …           …

Table 1. Example of a Program Index file (partial)

APPLICATION FUNCTIONS
Once the decision is made as to which SAS program and its outputs are to be entered into the Program Index file, the PI
Manager will provide the following functions to automatically update and track the programming and testing status of each
output in the Program Index file.

1. UPDATEING FILE LAST MODIFIED DATE
Collecting last modified system date and time for a SAS program, an output file, and a validation document in the Program
Index file would help us to keep track of the latest change made for individual file. Obviously, manually maintaining these
dates and times is a time consuming process, and as a result, the file may not be updated as quickly as the change is made.

The PI Manager cycles through each SAS program listed in the Program Index file and identifies whether the SAS program
exists on the server. If so, the PI Manager pulls the system date and time for each SAS program and automatically updates
the Program Index file with this information. It then searches for the corresponding testing documentation for each SAS
program. If it exists, it also pulls the last modified system date and time for the testing documentation and updates the
Program Index file with this data. Finally, it searches for the testing program, and again, updates the system date and time for
the testing program listed in the Program Index file.

2. CREATING FILE HYPERLINK
The PI Manager also automatically creates hyperlinks in the Program Index file to SAS programs, their outputs, their source
data sets, and all testing programs and validation documents associated with each SAS program, creating a complete, easy to
use reference for all study related programs, data, and outputs. This allows programmers and statisticians alike to quickly
locate an output, review a SAS program, or take a look at the source data used for a particular program.

3. RETRIEVING TESTING RESULT FROM VALIDATION DOCUMENT
An Excel-based standard validation form is used for every SAS program that produces deliverables to regulatory submissions
in our company. An overall validation result is given by the tester after all predefined test cases and any special test cases for
the particular SAS program are validated. It could be either “Pass” or “Fail”. The PI Manager would retrieve this overall
validation result from the individual validation document, then update it to the spreadsheet column in the Program Index file.
This would ensure that the overall testing result in the Program Index file truly reflects what it is in the individual validation
document.

4. TRACKING PROGRAM STATUS
The PI Manager performs a number of operations on the Program Index file in order to ascertain the status of a program. The
status of a program may be one of the followings:
     •    Programming in progress
     •    Testing in progress
     •    Testing complete (and program failed)
    •    Testing complete (and program passed)
    •    Program modified since last testing, re-testing needed


Report Category                               Criteria

Total Number of Output Files                  Total number of output files in the PI file

Number of Files in Programming                Program exists, no testing program and document

Number of Files in Testing                    Testing program exist, no overall testing result

Number of Files Testing Completed             Validation document contains overall testing result

Number of Files Requiring Retesting           Program system date and time is later than testing system date and time

Number of Files in Other Status               Number of files other than above status

Number of Files Passed Testing                Status of testing in validation document is “Pass”

Number of Files Failed Testing                Status of testing in validation document is “Fail”

Table 2. Programming and testing status definition criteria

After the PI Manager updates the last modified date and time for a SAS program, an output file, a testing program, and a
validation document, the PI Manager goes into the validation document for each SAS program and obtains the overall testing
result “Pass” or “Fail” and updates the “testing result” field of the Program Index file.

This information allows the PI Manager to update the “Program Status” column in the Program Index file based on the
available data. For example, if the SAS program does not yet exist, the status column is assigned to “Programming not
started.” If there is a SAS program and the testing is not completed, the Program Status is “Programming in progress.”

Most important, if the program system date and time is later than the validation documentation date and time, the PI Manager
flags the program, as it needs retesting.

5. GENERATING OVERALL VALIDATION REPORT
After the PI Manager updates the status of SAS programs and validation documents, it generates an overall validation report
for the study in a separate spreadsheet of the Program Index file. It summarizes total number of outputs and number and
percent of outputs for different development status. An example of the summary report provided by the PI Manager follows:
(see Table 3).

                                 SAS Program Validation Report

Total Number of Output Files:                                  245
Number of Files in Programming:                                9 (3%)
Number of Files in Validating:                                 4 (1%)
Number of Files Validated:                                     212 (86%)
Number of Files Need Re-Validation:                            20 (8%)
Number of Files in Other Status:                               13 (5%)
Number of Files Validation Passed:                             200 (81%)
Number of Files Validation Failed:                             32 (13%)

Table 3. Overall validation progress report generated by the PI Manager

In summary, the PI Manager improves the efficiency and accuracy in managing the testing status in many ways:
     • By automatically creating hyperlinks to SAS programs, their outputs, and all testing programs and validation
       documents associated with each SAS program, the PI Manager provides a fast, easy way for programmers or testers
       to view SAS programs, output, source data, or validation documentation.
     • Since the PI Manager retrieves the overall testing result “Pass” or “Fail” from individual testing documents and
       updates the worksheet based on this status, the project leader may at a glance, see the testing result of many
       programs in a clinical study.
    •    The PI Manager automatically identifies programs that require retesting due to modifications made since the last
         testing was completed. This provides confidence to the lead programmer that all changes to programs are tested
         before the output is delivered.
    •    PI Manager also provides a useful summary of the overall programming status for the study by providing the total
         number of programs and the number and percent of the programs that have been tested, that have passed or failed,
         and that require retesting due to modification. This is invaluable in aiding project leaders to keep track of overall
         programming status.
    •    Having an updated status of programming in a clinical trial helps budget resources and identify when timelines are at
         risk, as it provides the exact number of programs that still need to be programmed or tested. Programmers’ clients
         (statisticians and clinicians) appreciate a concise, data-based assessment of the programming status on a study or
         submission.

APPLICATION DESIGN AND IMPLEMENTATION
There are a few Java open source APIs available to access files in MS document format. One of the APIs is the Apache
                   1
Jakarta POI project . The POI project consists of APIs for manipulating various file formats based upon MS document format
using pure Java. HSSF is one of the POI APIs, providing a way to read spreadsheets. It can also create, modify, read, and
write Excel spreadsheets. Another open source, JExcel API2, also provides access to MS Excel files.




Figure 1. The PI Manager Class Diagram

The PI Manager is a standalone Java application. The current version of the PI Manager is developed on the top of HSSF.
The design of the PI Manager is simple. Figure 1 shows that the main class of this Java application is “PIManager.java”. It
controls the execution follow of the program. There are two Java Interfaces (“PiFile.java” and “QcForm.java”) defined
allowing different API implementations to be used in the future. In our current version of the PI Manager, we have
implemented it using both POI and JExcel.

The PI Manager uses a property file that initializes property values for its single instance. An instance of the PI Manager is
created for a single clinical study. The property file contains the information about the directory settings of the study so that
the PI Manager will go to correct locations to find SAS programs, output files, and validation documents for the study. Some of
the attributes of the Program Index file also are specified in the property file so that users may use different number of
columns, column headers, date formats, etc. in the Program Index file across studies. Since the PI Manager is implemented in
pure Java, it may be used in either UNIX or Windows environments.

Figure 2 shows that the PI Manager is installed in a central location (PIM_HOME). PIM_HOME contains executable jar files for
the PI Manager. The study specific Program Index file (Program_index_study_id.xls) is created in the study home directory. A
batch file (run_pim_study_id.bat) or UNIX shell script (run_pim_study_id.sh) and a study-related property file
(pim_property_study_id.properties) are created in the study level for initiating an instance of the PI Manager for the study.

  +-- PIM_HOME
  |      +-- build (ant build script)
  |      +-- lib (pim.jar, poi.jar)
  |      +-- src (Java source code)
  |      +-- javadoc
  …
    |
    +-- STUDY_HOME
          +-- analysis
           |      +-- final
           |            +-- createdata
           |                       +-- program (SAS programs)
           |                       +-- validation (validation programs and documents)
           |            +-- statdata
           |                       +-- raw (raw datasets)
           |                       +-- sdf (submission datasets)
           |            +-- tables
           |                       +-- output (output files)
           |                       +-- program (SAS programs)
           |                       +-- validation (validation programs and documents)
           |            +-- listings
           |                       +-- output (output files)
           |                       +-- program (SAS programs)
           |                       +-- validation (validation programs and documents)
           |            +-- graphs
           |                       +-- output (output files)
           |                       +-- program (SAS programs)
           |                       +-- validation (validation programs and documents)
          +-- docs
           | +-- Program_Index_study_id.xls
           |
          +-- utilities
                +-- run_pim
                    +-- run_pim_study_id.bat (run_pim_study_id.sh)
                    +-- config (pim_property_study_id.properties)
                    +-- logs (log files)

Figure 2. The PI Manager Setup for A Study


CONCLUSIONS
Using an Excel spreadsheet, Program Index, combined with a Java application, the PI Manager is a simple and
efficient solution for managing the validation of SAS programs that produce deliverables for regulatory
submissions in a rapid production environment. It improves the ease of validation management, reduces project
management overhead related to managing change to SAS programs, and makes the validation process quicker,
more efficient, and more reliable.

REFERENCES
1. http://jakarta.apache.org/poi/index.html
2. http://www.andykhan.com/jexcelapi/index.html

ACKNOWLEDGMENTS
We would like to thank Kurt Olson for reviewing this manuscript and providing valuable comments.
CONTACT INFORMATION
Your comments and questions are valued and encouraged. The authors may be contacted at:
      Tony Chang                                    Dana Soloff
      Amgen Inc.                                    Amgen Inc.
      One Amgen Center Dr.                          One Amgen Center Dr.
      Thousand Oaks, CA 91320                       Thousand Oaks, CA 91320
      Email: tochang@amgen.com                      Email: dsoloff@amgen.com

SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS
Institute Inc. in the USA and other countries.  indicates USA registration. Other brand and product names are
trademarks of their respective companies.

				
DOCUMENT INFO
Description: Clinical Trial Spreadsheet Excel document sample