clinical trial data validation using sas proc sql jie liu bios 524 project 2003, fall

15
Clinical Trial Data Validation Using SAS PROC SQL Jie Liu BIOS 524 Project 2003, Fall

Upload: paula-charles

Post on 27-Dec-2015

213 views

Category:

Documents


1 download

TRANSCRIPT

Page 1: Clinical Trial Data Validation Using SAS PROC SQL Jie Liu BIOS 524 Project 2003, Fall

Clinical Trial Data Validation Using SAS PROC SQL

Jie Liu

BIOS 524 Project

2003, Fall

Page 2: Clinical Trial Data Validation Using SAS PROC SQL Jie Liu BIOS 524 Project 2003, Fall

Background

• Toxicity data are collected for NCI Sponsored clinical trials at MCC. Data are monitored by NCI/CTEP quarterly.Toxicity (Protocol, CourseNo, Patient, Toxicity (Toxicity Code), Grade, Attribution, DLT, etc)

• Standard (for Toxicity Code and Grade): CTCAE 3.0 It lists hundreds of toxicity types and each toxicity type needs to be graded differently.

Page 3: Clinical Trial Data Validation Using SAS PROC SQL Jie Liu BIOS 524 Project 2003, Fall

Background

• For example:• Dry Skin: Grade can only be 1, 2, 3. 4 or 5• Fatigue: Grade can only be 1, 2, 3, 4. 5

• Errors can be made by data entry person easily.

• Challenge: How to detect errors?

Invalid value!

Invalid value!

Page 4: Clinical Trial Data Validation Using SAS PROC SQL Jie Liu BIOS 524 Project 2003, Fall

Sample Toxicity Data"Protocol" ,"CourseNo" ,"Patient ","CourseDate ","Toxicity ","Onset Date ","CDUS Tox Type Code

","Resolved ","AER Filed ","Grade ","Attribution ","Dose Limiting Tox? ","Serious ","Action ","Therapy ","Outcome ","A/N (obsolete) ",

"MCC0001 ",1 ,"PT0001 ",20030730,"DIARRHEA ",20030816,10012745 ,20030816,"N",1 ,"4","N",1 ,"1","1","1",-2.000 ,"MCC0001 ",1 ,"PT0001 ",20030730,"FATIGUE ",20030815,10016256 ,20030818,"N",1 ,"4","N",1 ,"1","1","1",-2.000 ,"MCC0001 ",1 ,"PT0001 ",20030730,"HEMOGLOBIN ",20030807,10018876 ,20030828,"N",1 ,"3","N",1 ,"1","1","1",-2.000 ,"MCC0001 ",1 ,"PT0001 ",20030730,"HYPOGLYCEMIA ",20030821,10020996 ,20030828,"N",1 ,"1","N",1 ,"1","1","1",-2.000

,"MCC0001 ",1 ,"PT0001 ",20030730,"LEUKOCYTES_(TOTAL_WBC) ",-6 ,10024285 ,20030811,"N",2 ,"1","N",1 ,"1","1","1",-

2.000 ,"MCC0001 ",1 ,"PT0001 ",20030730,"LEUKOCYTES_(TOTAL_WBC)

",20030811,10024285 ,20030814,"N",1 ,"3","N",1 ,"1","1","1",-2.000 ,"MCC0001 ",1 ,"PT0001 ",20030730,"LEUKOCYTES_(TOTAL_WBC)

",20030821,10024285 ,20030828,"N",2 ,"3","N",1 ,"1","1","1",-2.000 ,"MCC0001 ",1 ,"PT0001 ",20030730,"LYMPHOPENIA ",-6 ,10025327 ,20030807,"N",1 ,"1","N",1 ,"1","1","1",-2.000 ,"MCC0001 ",1 ,"PT0001 ",20030730,"LYMPHOPENIA ",20030807,10025327 ,20030814,"N",2 ,"3","N",1 ,"1","1","1",-2.000 ,"MCC0001 ",1 ,"PT0001 ",20030730,"LYMPHOPENIA ",20030821,10025327 ,20030828,"N",2 ,"3","N",1 ,"1","1","1",-2.000 ,"MCC0001 ",1 ,"PT0001 ",20030730,"NEUTROPENIA ",-6 ,90004008 ,20030807,"N",2 ,"1","N",1 ,"1","1","1",-2.000 ,"MCC0001 ",1 ,"PT0001 ",20030730,"NEUTROPENIA ",20030807,90004008 ,20030811,"N",3 ,"3","N",1 ,"5","1","1",-2.000 ,"MCC0001 ",1 ,"PT0001 ",20030730,"PAIN OTHER-ACHES ",20030802,90004082 ,20030803,"N",1 ,"4","N",1 ,"1","1","1",-

2.000 ,"MCC0001 ",1 ,"PT0001 ",20030730,"PAIN OTHER-ACHES ",20030815,90004082 ,20030816,"N",1 ,"4","N",1 ,"1","1","1",-

2.000 ,"MCC0001 ",1 ,"PT0001 ",20030730,"PLATELETS ",-6 ,10035528 ,20030807,"N",1 ,"1","N",1 ,"1","1","1",-2.000 ,"MCC0001 ",1 ,"PT0001 ",20030730,"PLATELETS ",20030821,10035528 ,-6 ,"N",1 ,"3","N",1 ,"1","1","2",-2.000 ,"MCC0001 ",2 ,"PT0001 ",20030828,"CREATININE ",20030828,10005483 ,20030904,"N",1 ,"3","N",1 ,"1","1","1",-2.000 ,"MCC0001 ",2 ,"PT0001 ",20030828,"HYPOGLYCEMIA ",20030904,10020996 ,20030911,"N",1 ,"1","N",1 ,"1","1","1",-2.000

Page 5: Clinical Trial Data Validation Using SAS PROC SQL Jie Liu BIOS 524 Project 2003, Fall

CTCAE 3.0Common Terminology Criteria for Adverse Events Adverse Events Category, AE/Supra-ordinate Term, Select AE, MedDRA Preferred Term, MedDRA Code, Grade, Description"ALLERGY/IMMUNOLOGY","Allergic reaction/hypersensitivity (including drug fever)","","Hypersensitivity

NOS","10020755","1","Transient flushing or rash; drug fever <38 degrees C (<100.4 degrees F)""ALLERGY/IMMUNOLOGY","Allergic reaction/hypersensitivity (including drug fever)","","Hypersensitivity NOS","10020755","2","Rash;

flushing; urticaria; dyspnea; drug fever >=38 degrees C (>=100.4 degrees F)""ALLERGY/IMMUNOLOGY","Allergic reaction/hypersensitivity (including drug fever)","","Hypersensitivity

NOS","10020755","3","Symptomatic bronchospasm, with or without urticaria; parenteral medication(s) indicated; allergy-related edema/angioedema; hypotension"

"ALLERGY/IMMUNOLOGY","Allergic reaction/hypersensitivity (including drug fever)","","Hypersensitivity NOS","10020755","4","Anaphylaxis"

"ALLERGY/IMMUNOLOGY","Allergic reaction/hypersensitivity (including drug fever)","","Hypersensitivity NOS","10020755","5","Death""ALLERGY/IMMUNOLOGY","Allergic rhinitis (including sneezing, nasal stuffiness, postnasal drip)","","Rhinitis allergic

NOS","10039087","1","Mild, intervention not indicated""ALLERGY/IMMUNOLOGY","Allergic rhinitis (including sneezing, nasal stuffiness, postnasal drip)","","Rhinitis allergic

NOS","10039087","2","Moderate, intervention indicated""ALLERGY/IMMUNOLOGY","Allergy/Immunology - Other (Specify, __)","","Not available","90004000","1","Mild""ALLERGY/IMMUNOLOGY","Allergy/Immunology - Other (Specify, __)","","Not available","90004000","2","Moderate""ALLERGY/IMMUNOLOGY","Allergy/Immunology - Other (Specify, __)","","Not available","90004000","3","Severe""ALLERGY/IMMUNOLOGY","Allergy/Immunology - Other (Specify, __)","","Not available","90004000","4","Life-threatening; disabling""ALLERGY/IMMUNOLOGY","Allergy/Immunology - Other (Specify, __)","","Not available","90004000","5","Death""ALLERGY/IMMUNOLOGY","Autoimmune reaction","","Autoimmune disorder NOS","10003815","1","Asymptomatic and serologic or

other evidence of autoimmune reaction, with normal organ function and intervention not indicated""ALLERGY/IMMUNOLOGY","Autoimmune reaction","","Autoimmune disorder NOS","10003815","2","Evidence of autoimmune reaction

involving a non-essential organ or function (e.g., hypothyroidism)""ALLERGY/IMMUNOLOGY","Autoimmune reaction","","Autoimmune disorder NOS","10003815","3","Reversible autoimmune reaction

involving function of a major organ or other adverse event (e.g., transient colitis or anemia)""ALLERGY/IMMUNOLOGY","Autoimmune reaction","","Autoimmune disorder NOS","10003815","4","Autoimmune reaction with life-

threatening consequences""ALLERGY/IMMUNOLOGY","Autoimmune reaction","","Autoimmune disorder NOS","10003815","5","Death""ALLERGY/IMMUNOLOGY","Serum sickness","","Serum sickness","10040400","3","Present""ALLERGY/IMMUNOLOGY","Serum sickness","","Serum sickness","10040400","5","Death"

Page 6: Clinical Trial Data Validation Using SAS PROC SQL Jie Liu BIOS 524 Project 2003, Fall

Solution

• A SAS program is developed to detect grade errors and run summary analysis.

• Files needed• AEGrades.txt is CTCAE 3.0 standard and used to do data

validation.• TX.txt is the file need to be checked.

• Constraints:• Toxicity Code is from CTCAE 3.0• Grade is from CTCAE 3.0 and 0 < Grade <= 5

Page 7: Clinical Trial Data Validation Using SAS PROC SQL Jie Liu BIOS 524 Project 2003, Fall

PROC SQL

• Structured Query Language (SQL).

• PROC SQL is a powerful Base SAS PROC combining the functionality of the DATA and PROC Steps into a single procedure.

• In some cases is a more efficient alternative to traditional SAS code.

Page 8: Clinical Trial Data Validation Using SAS PROC SQL Jie Liu BIOS 524 Project 2003, Fall

SQL procedure enables you to• Extract data from Data set• Create a new data set• Select unique values of one or more columns• Subsetting and calculating• And more…

Page 9: Clinical Trial Data Validation Using SAS PROC SQL Jie Liu BIOS 524 Project 2003, Fall

Syntax

PROC SQL; /*Begin SQL*/

QUIT; /* End */

SQL commands

Page 10: Clinical Trial Data Validation Using SAS PROC SQL Jie Liu BIOS 524 Project 2003, Fall

SQL Example

• Select Statementselect * All variables in the table

from toxicity where patient = ‘JL’;

• Create TABLE Statementcreate table newtox asselect patient, tox_code, grade,

attributionfrom toxicity;

Table name

Field name

New table name

Table name

Page 11: Clinical Trial Data Validation Using SAS PROC SQL Jie Liu BIOS 524 Project 2003, Fall

SQL Example

select distinct '1' as ErrorID, Patient, CourseNo, CDUS_Tox_Type_Code as Tx_Code, Grade, 'Grade must not be blank' as ERRMSG from NewAE where Grade is NULL

union

select distinct '2' as ErrorID, Patient, CourseNo, CDUS_Tox_Type_Code as Tx_Code , Grade, 'Grade must not be greater than 5' as ERRMSG from NewAE where Grade > 5 and Grade is not NULL

Combine the results of two SQL commands together

Page 12: Clinical Trial Data Validation Using SAS PROC SQL Jie Liu BIOS 524 Project 2003, Fall

SAS Output

Note: Patient, CourseNo, Tx_Code and Grade together is primary key so we are able to tell the error’s location by the primary key.

Page 13: Clinical Trial Data Validation Using SAS PROC SQL Jie Liu BIOS 524 Project 2003, Fall

Summary Analysis

proc tabulate data=NewAeTx;

class Toxicity Attribution Grade;

table Toxicity*Attribution, Grade;

title “Toxicity Summary";

format Attribution Attrifmt.;

run;

Page 14: Clinical Trial Data Validation Using SAS PROC SQL Jie Liu BIOS 524 Project 2003, Fall

SAS Output

Page 15: Clinical Trial Data Validation Using SAS PROC SQL Jie Liu BIOS 524 Project 2003, Fall

Questions?