clinical trial data validation using sas proc sql jie liu bios 524 project 2003, fall
TRANSCRIPT
![Page 1: Clinical Trial Data Validation Using SAS PROC SQL Jie Liu BIOS 524 Project 2003, Fall](https://reader035.vdocuments.us/reader035/viewer/2022072011/56649e395503460f94b2acba/html5/thumbnails/1.jpg)
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](https://reader035.vdocuments.us/reader035/viewer/2022072011/56649e395503460f94b2acba/html5/thumbnails/2.jpg)
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](https://reader035.vdocuments.us/reader035/viewer/2022072011/56649e395503460f94b2acba/html5/thumbnails/3.jpg)
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](https://reader035.vdocuments.us/reader035/viewer/2022072011/56649e395503460f94b2acba/html5/thumbnails/4.jpg)
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](https://reader035.vdocuments.us/reader035/viewer/2022072011/56649e395503460f94b2acba/html5/thumbnails/5.jpg)
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](https://reader035.vdocuments.us/reader035/viewer/2022072011/56649e395503460f94b2acba/html5/thumbnails/6.jpg)
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](https://reader035.vdocuments.us/reader035/viewer/2022072011/56649e395503460f94b2acba/html5/thumbnails/7.jpg)
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](https://reader035.vdocuments.us/reader035/viewer/2022072011/56649e395503460f94b2acba/html5/thumbnails/8.jpg)
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](https://reader035.vdocuments.us/reader035/viewer/2022072011/56649e395503460f94b2acba/html5/thumbnails/9.jpg)
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](https://reader035.vdocuments.us/reader035/viewer/2022072011/56649e395503460f94b2acba/html5/thumbnails/10.jpg)
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](https://reader035.vdocuments.us/reader035/viewer/2022072011/56649e395503460f94b2acba/html5/thumbnails/11.jpg)
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](https://reader035.vdocuments.us/reader035/viewer/2022072011/56649e395503460f94b2acba/html5/thumbnails/12.jpg)
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](https://reader035.vdocuments.us/reader035/viewer/2022072011/56649e395503460f94b2acba/html5/thumbnails/13.jpg)
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](https://reader035.vdocuments.us/reader035/viewer/2022072011/56649e395503460f94b2acba/html5/thumbnails/14.jpg)
SAS Output
![Page 15: Clinical Trial Data Validation Using SAS PROC SQL Jie Liu BIOS 524 Project 2003, Fall](https://reader035.vdocuments.us/reader035/viewer/2022072011/56649e395503460f94b2acba/html5/thumbnails/15.jpg)
Questions?