Handling the Dates and Times in Clinical Trial Data Collection Using SAS

In clinical trials, we always need to deal with the dates and times in data collection. For example, we will need to record the date of signing the informed consent form, date/time of medical history events, date/time of study drug administration, date/time of adverse events and concomitant medications. We also have to perform checks to make sure that these dates are in chronicle logic order, for example, the an event stop date/time must be after the event start date/time.

These date/time variables may be recorded in different ways and different formats. There may be partial (incomplete) dates recorded. An analysis may require the comparison of a date variable with a cut off date for the purpose of the annual safety report and for interim analysis. All of these will require good handling of the date / time variables. SAS software provides sufficient functions and formats for us to handle the dates/times. Here are some of the most commonly used date/time handling techniques with example codes.  

*Convert the date in character format to numeric format;
data test;
  date='05/04/00';
  time='10:05';
run;

data new; set test;
  sasdate1=input(date, mmddyy8.);
  sasdate2=sasdate1;
  sastime1=input(time, time5.);
  sastime2=sastime1;
  datetim1=input(put(sasdate1,date7.)||':'||time,datetime16.);
  datetim2=datetim1;
  ** created sasdate2, sastime2, datetim2 for display purposes only **;
run;

proc print data=new;
  var sasdate1 sasdate2 sastime1 sastime2 datetim1 datetim2;
  format sasdate2 mmddyy8.
         sastime2 hhmm5.
         datetim2 datetime16.;
run;


*Convert the time in character format to numeric format;
data c_time;
input c_time $;
n_time=input(c_time, time5.);
cards;
07:33
11:32
07:29
07:10
07:15
00:11
10:29
;
proc print;
     format n_time time5.;
run;

*Convert the time in Character format (without leading zero) to numeric format;
data have;
input raw_time $;
cards;
0733
1132
0729
710
715
11
1029
;
data want;
   set have;
    if length(raw_time)=2 then time=input(('0'||':'||raw_time),time5.);
     else
     time = input(substr(raw_time,1,length(raw_time)-2)||':'||substr(raw_time,length(raw_time)-1),time5.);
    format time time5.;
proc print;
run;


*Retrieve Date or Time from Datetime field
in the examples below, it does not matter if it ":", "T", or '' between date and time part in datetime field. 

data temp;
  date_time1 = "19DEC2010:20:10:10"dt;
  date_time2 = "19DEC2010T20:10:10"dt;
  date_part1 = datepart(date_time1);
  time_part1 = timepart(date_time1);
  date_part2 = datepart(date_time2);
  time_part2 = timepart(date_time2);
run;

proc print;
   format date_time1 date_time2 datetime25.6 
             date_part1 date_part2 date9. 
             time_part1 time_part2 time.;
run;


*Compare a Date Variable with a Fixed Date;
data temp;
  date_time = "19DEC2010:20:10:10"dt;
  date_part = datepart(date_time);
  if date_part >'01OCT2014'd then flag=1;
  else if date_part <= '01OCT2014'd then flag=0;
run;

*Handling the partial dates;

*Reading in the date and partial dates; AEDate is character;
data partialdate;
 input @1 Id @4 AEdate $10. @15 AEdate_p $10.;
datalines;
01 12/23/1964
02 11/22/1975    
03            11/UN/1980
04 08/15/1987
;
*Combine the full date and particial date variables;
data partialdate;
  set partialdate;
  If AEDATE ne '' then DISPLAYDATE=AEdate; 
  else displaydate=aedate_p;
run;
proc print;
run;


*Display the date in date9. format with partial date; AEdate variable is numeric;
data partialdate;
 input @1 Id @4 AEdate mmddyy10. @15 AEdate_p $10.;
datalines;
01 12/23/1964
02 11/22/1975    
03            11/UN/1980
04 08/15/1987
;
proc format ;  
  value $valmn '01'='JAN' '02'='FEB' '03'='MAR' '04'='APR'                      '05'='MAY' '06'='JUN' '07'='JUL'
               '08'='AUG' '09'='SEP' '10'='OCT' '11'='NOV'                      '12'='DEC'  'UN'='' ;
quit ;

data newdate;
  set partialdate;
  If AEDATE ne . then DISPLAYDATE=put(aedate,date9.) ;
  else if aedate_p ne '' then 
       DISPLAYDATE= trim(  substr(aedate_p,4,2)||put( substr(aedate_p,1,2), $valmn.)||substr(aedate_p,7) ) ;
  if displaydate=:'UN' then displaydate=trim(substr(displaydate,3)) ;
run;

proc print;
  format aedate mmddyy10.;
run;

data partialdate;
 input @1 Id @4 AEdate mmddyy10. @15 AEdate_p $10.;
datalines;
01 12/23/1964
02 11/22/1975    
03            UN-JUL-1980
04 08/15/1987
;

data partialdate;
  set partialdate;
  If aedate ne . then displaydate=put(aedate, date9.) ;
  else if aedate_p ne '' then displaydate= compress(aedate_p,'-') ;
  if displaydate=:'UN' then displaydate=substr(displaydate,3) ;
   displaydate=upcase(displaydate) ;
run;
proc print;
  format aedate mmddyy10.;
run;

*Dynamic Date Format;
proc format;
      value readdate 1='date7.'
                     2='mmddyy8.';
   run;

   options yearcutoff=1920;

   data fixdates;
      length jobdesc $12;
      input source id lname $ jobdesc $ start $;
      dateinformat=put(source, readdate.);
      newdate = inputn(start, dateinformat);
      datalines;
   1 1604 Ziminski writer 09aug90
   1 2010 Clavell editor 26jan95
   2 1833 Rivera writer 10/25/92
   2 2222 Barnes proofreader 3/26/98
   ;
proc print;
 format  newdate mmddyy8.;
run;


*Deal with time variable in SAS/Graph;
data one;
   input startime time5. count;
   if startime gt '12:00't then date='30sep92'd;
      else date='01oct92'd;
   datetime=dhms(date,hour(startime),minute(startime),
            second(startime));
   cards;
16:00 12.3
17:00 5.7
18:00 8.6
19:00 9.0
20:00 15.7
21:00 10.5
22:00 8.1
23:00 1.5
0:00  11.3
1:00  6.6
2:00  3.5
3:00  7.6
4:00  2.4
5:00  13.8
6:00  14.0
7:00  4.9
8:00  5.0
run;

proc print;
   var startime datetime;
run;

proc gplot data=one;
   plot count*datetime
        / haxis='30sep92:16:00'dt to '01oct92:08:00'dt
                by hour2;
   format datetime tod5.;
run;


*Working with time variable;
*randtime is a Character time variable with letter 'T'
*c_time is a character variable with T removed or compressed
*T_time is a numeric time variable. to display, the time. format is needed. 
data randtime;
  randtime = 'T20:10:10';
  C_time=compress(randtime, 'T');
  T_time=input(C_time,time.);
run;

proc print;
  format t_time time.;
run;

References: 

• The Essentials of SAS® Dates and Times 
• WORKING WITH SAS® DATE AND TIME FUNCTIONS
• SAS Learening Module: Using dates
• SAS Date, Datetime, and Time Values
• How to Read, Write, and Manipulate SAS® Dates

Data Transport Across Different Platforms: Creating and Reading the SAS transport files (.xpt, .cpt, .dat)

Since there are different computer platforms (Windows - PC, Unix, Linux, OpenVMS), it is often necessary to create the transport file so that the data file created in one platform can be used in another platform. SAS is the primary data format used in pharmaceutical/biotech companies and SAS data format is generally the data format in regulatory submissions. In an early posting "Submit the Clinical Trial Datasets to FDA: Using the right .xpt file format", I discussed that for FDA submission, the SAS transport file must be generated using xport engine (i.e, using PROC COPY).

Creating .xpt file using PROC COPY

While possible, this should be used a standard way to create the SAS transport file. 

libname source 'SAS-library-on-sending-host';   /*indicating where the original SAS data sets (in .sas7bdat format) are*/

libname xptout xport 'filename-on-sending-host'; /*indicating where the .xpt files to be stored */

proc copy in=source out=xptout memtype=data;

run;

Reading .xpt file generated with PROC COPY

For recipients, the SAS transport file in .xpt format needs to be converted back to SAS data format so that the further data manipulation / analysis can be performed. To read the .xpt file generated using PROC COPY, we will still use PROC COPY:

libname sasfolder "SAS-library-on-receiving-host"; /*indicating where the converted SAS data sets (in .sas7bdat format) to be stored*/

libname xptfile xport '\folder\xyz.xpt' access=readonly; /*indicating which the .xpt files to be read in */

proc copy inlib=xptfile outlib=sasfolder;

run;

We can also use SAS data step to read the .xpt file. Or we may just read .xpt file in without creating a permanent SAS data set.  

libname xportin xport '\folder\transport-file-name.xpt';

libname target '\folder\';

data target.transport-file-name; *create a permanent SAS data set;

set xportin.transport-file-name ;

run;

data transport-file-name;  *create a temporary SAS data set;

set xportin.transport-file-name;

run;

Creating .xpt file using PROC CPORT

In SAS, the transport file can be generated with PROC CPORT (not recommended for regulatory submission) with the following statement.  

          filename xptfile "c:\temp\test.xpt";

libname source "c:\sourcedata\";

***For converting a data set;

proc cport library=source file=xptfile memtype=data;

run;

***For converting a format catalog;

proc cport library=source file=xptfile memtype=catalog;

run;

If we strictly follow the name convention, the SAS transport file created with PROC CPORT should use the file extension .cpt (versus .xpt for SAS transport file generated with PROC XPORT). Unfortunately, people may not follow the conversion and use .xpt for SAS transport files generated either with PROC XPORT or PROC CPORT). 

Reading .xpt file generated with PROC CPORT

To read the .xpt file created by PROC CPORT, PROC CIMPORT:

FILENAME xpt 'transport_file_name.xpt';


libname dest 'destination_directory_name';


PROC CIMPORT INFILE= xpt LIBRARY=Dest; 

run;

Read the transport file in other format/extensions (.dat, .cpt). 

We may also receive data in other format for example, the transport data file may be in .dat format. Data with .DAT extension indicates a data file that has arbitrary data. That means it’s not associated with any one particular program or application. The following program can be used to read the data into SAS. 

libname outlib 'C:\TEMP';

filename infile 'TAL050003XF.dat';

proc cimport library=outlib file=infile;

run;
The file with .cpt extension is the transport file generated using SAS PROC CPORT as described above. 

Adverse Event of Special Interest (AESI), Standardized MedDRA Query (SMQs), Customer Queries (CQs), and SAS Programming

When we perform the safety analyses for clinical trial data or post-marketing pharmacovigilence data, one of the common approach is to identify and summarize the adverse events of special interest (AESI). FDA guidance for industry: E2F Development Safety Update Report defined the AESI as following:

“Adverse event of special interest: An adverse event of special interest (serious or non-serious) is one of scientific and medical concern specific to the sponsor’s product or program, for which ongoing monitoring and rapid communication by the investigator to the sponsor can be appropriate. Such an event might warrant further investigation in order to characterize and understand it. Depending on the nature of the event, rapid communication by the trial sponsor to other parties (e.g., regulators) might also be warranted. (Based on CIOMS VI)”

ICH Topic E2F Development Safety Update Report also indicated the importance of identifying the AESIs:

“If important and appropriate, the report should also include adverse reactions of special interest within the line listings and adverse events of special interest in summary tabulations. The basis for selection of such events/reactions should be explained. “

Since the same AESI may include many verbatim (reported) terms or preferred (coded) terms, the first step of the analysis of AESI is to define the list of terms that will be considered as the specific AESI.

I used to do a large phase III study in Irritable Bowel Syndrome (IBS). The protocol specified the diarrhea and rectal bleeding events as AESI. In developing the statistical analysis plan (SAP), the following tables were included to to identify the diarrhea events and rectal bleeding events. .

Adverse Event of Special Interest

Group term	Preferred term
Diarrhea	Diarrhea NOS
	Loose stools, stools loose
	Loose bowel, loose bowels
	Watery diarrhea (diarrhoea)
	Stools watery
	Gastroenteritis
	Gastroenteritis NOS
	Gastroenteritis non-infectious
Rectal bleeding	Rectal bleeding
	Rectal haemorrhage
	Anal haemorrhage
	GI h(a)emorrhage
	GI h(a)emorrhage NOS
	Diarrhea haemorrhagic
	Haemorrhage rectum
	Blood in stool
	Occult blood
	Melaena
	Hematochezia
	Lower GI bleeding (not found)

In 131st Meeting of the Vaccines and Related Biological Products Advisory Committee (VRBPAC) in 2012, the adverse event of special interest regarding autoimmune events was discussed. The meeting minutes explained how the autoimmune events were identified:

"The first was adverse events of special interest. This involved the identification, categorization and analysis of adverse events in the safety database. It was performed using a list of autoimmune and inflammatory conditions. The system was used for all trials in the HEPLISAV clinical program including the Phase 3 trials."

In FDA’s advisory committee meeting to discuss BioMarin Vimizim (elosulfase alfa) for the treatment of Mucopolysaccharidosis Type IVA (Morquio A syndrome), the AESI of hypersensitivity and anaphylactic reaction events was discussed:

“Potential Hypersensitivity AEs were identified by utilizing the broad Anaphylactic Reaction algorithmic Standardized MedDRA query (SMQ) and the broad Angioedema SMQ, which represent a broad range of terms to detect signals possibly indicative of hypersensitivity. Hypersensitivity AEs were reported in 16.2% of subjects in the Proposed Dose Population. No correlation was found between higher titers of anti-BMN 110 antibodies and increased incidence or severity of hypersensitivity AEs. The Warnings and Precautions section of the proposed prescribing information includes language regarding the clinical study experience with anaphylaxis and severe allergic reactions and provides recommended management of and preventive measures for severe allergic-type hypersensitivity reactions. o BioMarin reviewed all reported adverse events against the NIAID/FAAN 2006 criteria for anaphylaxis (Sampson H et al, 2006). Of the 235 subjects exposed to BMN 110 in the development program, the sponsor identified  16 (6.8%) cases consistent with NIAID/FAAN 2006 criteria for anaphylaxis. The reactions were successfully managed with infusion rate adjustments and/or medical intervention, and all but 2 subjects continue to receive subsequent BMN 110 infusions. This rate of anaphylaxis is comparable to other enzyme replacement therapies”

To discuss the AESI, we must know the concept of SMQ. To assist in standardizing the AESI identification process, Standardised MedDRA Queries (SMQs) are developed to facilitate retrieval of MedDRA-coded data as a first step in investigating drug safety issues in pharmacovigilance and clinical development. SMQs are validated, pre-determined sets of MedDRA terms grouped together after extensive review, testing, analysis, and expert discussion. SMQs are a unique feature of MedDRA and provide a strong tool to support safety analysis and reporting. The SMQ topics are intended to address the important pharmacovigilance topics needed by regulatory and industry users. SMQs have been developed with the CIOMS Working Group on Standardised MedDRA Queries that provides pharmacovigilance expertise and validation of SMQs. The SMQs are maintained with each release of MedDRA dictionary by the MSSO.

As of 1 September 2014, there are almost 100 SMQs available. The list of available SMQs can be found here from MSSO website.

If we need to identify a risk or AESI, if SMQ is available, SMQ should be used. If SMQ is not available, the customer-defined list of preferred terms (PTs), so-called CQs (customer queries), will be needed. For three AESIs discussed above, SMQs are available for Diarrhea (as Noninfectious diarrhoea) and Hypersensitivity, but not available for rectal bleeding events. In an ophthalmology paper, both SMQ and customer-defined PTs (CQs) were used to identify various risks. Similarly, in a T2DM study, both SMQ and customer-defined PTs were used to identified various AESIs.

• Introductory Guide for Standardised MedDRA Queries (SMQs) Version 16.0
• MedDRA®: Safety Data Analysis and SMQs MedDRA^® DATA RETRIEVAL AND PRESENTATION:  POINTS TO CONSIDER 

In practice, once we have the database containing all AEs and we have a list of preferred terms (either from SMQ or from the customer-defined [CQs]) to identify the AE of special interest, a simple SAS program with PROC SQL can be used to identify the reported events from the database.

Here the AE database is the base table. SMQ preferred terms or customer-defined preferred terms (CQs) is the look-up table. 

proc sql;

    ***Create a look up table where pt is preferred term;

    create table LookupTable as

    select distinct range, pt

    from smq

    where range = 'Narrow';

    ***Select records based on the look-up table;

    create table matched_terms as

    select subjid, aeterm, pt label='Preferred Term'

    from ae

    where upcase(pt) in (select upcase(pt) from LookupTable);

quit;

proc print data=matched_terms label noobs;

run;

The following papers discussed how to use SAS to perform the safety analysis using SMQ.

• Practice of SMQs for Adverse Events in Analysis of Safety Data and Pharmacovigilance
• Adverse Events of Special Interest and MedDRA Upgrades: A Dilemma and Proposed Solution

CDISC “Analysis Data Model (ADaM) Data Structure for Adverse Event Analysis” discussed MedDRA SMQ, included a specific section “4.1.9 MedDRA Query Variables”, and provided an example for analysis of Hemorrhages using SMQ. 

Dose Limiting Toxicity (DLT) and Common Toxicity Criteria (CTC) / Common Terminology Criteria for Adverse Events (CTCAE)

For the early clinical phase trials, especially first-in-man oncology studies, the major objective is usually to identify a safe dose, such as the MTD (maximal tolerated dose),  the highest dose that can be given with acceptable toxicity, and establish the safety profile. To identify the MTD, dose escalation studies are usually conducted. The determination of the MTD is based on the occurrence of the DLT (dose limiting toxicity), Dose-Limiting toxicity is defined to be a toxicity that prevents further administration of the agent at that dose level. One of the criteria for FDA to approve a Breakthrough Therapy designation for an experimental drug is that the experimental drug can significantly improve safety profile compared to available therapy (e.g., less dose-limiting toxicity for an oncology agent), with evidence of similar efficacy.

The choice of DLT (dose-limiting toxicity) may vary from study to study based on the natural history of the disease and the level of toxicity expected from standard therapy. For example, one might accept a greater degree of toxicity for a patient with end-stage cancer who has no other options, but less toxicity for a healthy individual getting a preventive medicine. 

The DLT has close relationship with the CTC (common toxicity criteria) and CTCAE (common terminology criteria for adverse events).

CTC (Common Toxicity Criteria) is the precursor of what is today named the Common Terminology Criteria for Adverse Events (CTCAE). The original CTC was developed by the Cancer Therapy Evaluation Program (CTEP) of the National Cancer Institute (NCI) in 1983 to aid in the documentation and analysis of adverse effects of chemotherapy. CTC, like CTCAE, included terms and a severity grading scale with descriptions of the allowed grades of each term. Starting from v3, the CTC was replaced by CTCAE v3.

CTCAE is a list of terms (adverse events) commonly encountered in oncology interventions. Each AE term is defined and associated with a rating scale of severity that indicates the severity of the AE. The rating scale is used in the definition of protocols parameters (Eligibility; Maximum Tolerated Dose; Dose modification; etc) and indicates what is reasonable to document, report, and analyze for patient safety oversight based on current oncology research interventions. CTCAE is available only in English and the most recent version of CTCAE is verion 4.0. In the new CTCAE v4.0, the AE terms are organized by the System Organ Classes (SOCs) defined by the Medical Dictionary for Regulatory Activities (MedDRA). CTCAE has been developed from the earlier vocabulary known as CTC (Common Toxicity Criteria).

While CTC / CTCAE were developed by NCI, they were being used for clinical trials outside cancer trials such as AIDS/HIV trials, hypertension trials, and others.

The definition of Dose-limiting Toxicity (DLT) is determined by the individual protocol, not the CTC or CTCAE. Although it would be convenient to assume that all Grade 3 adverse events based on CTC or CTCAE represent dose limiting toxicities, this may not be appropriate. Grade 3 or 4 adverse events (based on CTC or CTCAE) of complications such as nausea and vomiting can be controlled with appropriate supportive care measures and may not constitute DLTs. Prolonged grade 2 toxicities can be considered DLTs depending on the schedule of drug administration. Acceptable DLTs or adverse events vary with the patient population and the anticipated outcome of the treatment. More severe adverse events may be acceptable with a potentially curative regimen than with a palliative treatment.

Typically in clinical trials, investigators will base their clinical judgment to grade all reported adverse events (AEs) during the study with three categories: mild, moderate, and severe.

Mild: An event that is easily tolerated by the subject, causing minimal discomfort and not             interfering with everyday activities.

Moderate: An event that is sufficiently discomforting to interfere with normal everyday activities.

Severe: An event that prevents normal everyday activities.

For oncology trials, the Grading should be based on CTCAE as following:

Grade 0 No Adverse Event	Sign/symptom within normal limits
Grade 1 Mild Adverse Event	Minor Mild symptoms and intervention not indicated Non-prescription intervention indicated No specific medical intervention Asymptomatic laboratory finding only Radiographic finding only Marginal clinical relevance
Grade 2 Moderate Adverse Event	Intervention indicated Minimal, local, noninvasive intervention (e.g. packing, cautery) Limiting instrumental ADL (e.g., shopping; laundry; transportation; ability to conduct finances)
Grade 3 Severe Adverse Event	Medically significant but not life-threatening Inpatient or prolongation of hospitalization indicated Important medical event that does not result in hospitalization but may jeopardize the patient or may require intervention either to prevent hospitalization or to prevent the AE from becoming life-threatening or potentially resulting in death Disabling - results in persistent or significant disability or incapacity Limiting self care ADL (e.g., getting in and out of bed; dressing; eating; getting around inside; bathing; using the toilet)
Grade 4 Life-threatening Adverse Event	Life-threatening consequences       Urgent intervention indicated Urgent operative intervention indicated Patient is at risk of death at the time of the event if immediate intervention is not undertaken
Grade 5 Fatal Adverse Event	Death

To map the CTCAE grading to AE severity / intensity, any AE graded as 1 using CTCAE can be categorized as mild, 2 be categorized as moderate and ≥3 be categorized as severe.

There are considerable discussions about the standardization in determining the dose limiting toxicities.

§         Towards new methods for the determination of dose limiting toxicities and the assessment of the recommended dose for further studies of molecularly targeted agents – Dose-Limiting Toxicity and Toxicity Assessment Recommendation Group for Early Trials of Targeted therapies, an European Organisation for Research and Treatment of Cancer-led study

§         Defining dose-limiting toxicity for phase 1 trials of molecularly targeted agents: Results of a DLT-TARGETT international survey

§         Definition of dose-limiting toxicity in phase I cancer clinical trials of molecularly targeted agents

§         Heterogeneity in the definition of dose-limiting toxicity in phase I cancer clinical trials of molecularly targeted agents: A review of the literature

§         Criteria for defining dose-limiting criteria

In a paper by Paoletti et al “Defining dose-limiting toxicity for phase 1 trials of molecularly targeted agents: Results of a DLT-TARGETT international survey”, it was stated “DLT is traditionally defined as any grade 3–4 non-haematological or grade 4 haematological toxicity at least possibly related to the treatment, occurring during the first cycle of treatment. Some adjustments to this definition have been widely accepted, such as febrile neutropenia, or neutropenia grade 4 lasting more than 7 days or abnormal laboratory values rated as a DLT only in the presence of clinical symptoms.”

In study by Angevin et al “Phase I Study of Dovitinib (TKI258), an Oral FGFR, VEGFR, and PDGFR Inhibitor, in Advanced or Metastatic Renal Cell Carcinoma”, the following DLT criteria were provided in the study protocol appendix.