Clinical Data Management (CDM)  

1a. General Tips - i) Report for each issue - data set name, patient number, visit date, variables checked, supporting variables and subset condition, ii) Apply inverse of good data condition, iii) Use CATX(' ', ' ', N1, C1) to concatenate text, numeric or character variables into a query text variable to be displayed.

 Identify if global solution, which effects all sites such as age calculation, or local solution, which is focused on selected sites, is required.

1b. Scope Key Performance Indicator - Summary level measurement, at least one failed patient out of x edit checks, = # of failed edit checks/Total # of edit checks

 1c. Impact Detail level measurement – systematic or localized problem based on # of patients affected?, Overall = # of failed records/(Total # of records x Total # of edit checks) and By edit check = # of failed records/Total # of records

 IF <NUMERIC_VAR> NOT BETWEEN <MIN> AND <MAX> AND <NUMERIC_VAR> IS NOT MISSING THEN display key and numeric variables

IF <CHARACTER_VAR> ^IN ('A', 'B', 'C') THEN display key and char vars 

 IF VERIFY(TRIM(<CHARACTER_VAR), 'ABC') ^=0 THEN display key and char vars

IF INDEXW('BASELINE WEEK2 WEEK4', COMPRESS(VISIT)) = 0 THEN display key and visit variables

 21. Checking across two visits, objective is to compare values on the same record - See SAS Paper

 22. Checking across two or more visits, create new variable names for each visit by patient, use PREFIX option

 The alternative for creating several variable is within a DATA step, to subset for each visit and rename each new variable

 23. Converting multiple to single record per patient using an option below.  Add _single as suffix and keep selected variables.

 a) NODUPKEY for same values across visits for each patient
 b) PROC SORT, descending to get non-missing same value, PROC SORT NODUPKEY by patient
 c) MINIMUM values
 d) MINIMUM start date and MAXIMUM end date

 e) FIRST. or LAST. in a DATA Step to get first non-missing value based on grouping variables

1. Apply required condition as WHERE dataset option to first exclude any non-checked records.

2. Initially assign CHECKFLG to missing for each patient.  RETAIN CHECKFLG; IF FIRST.SUBJID THEN CHECKFLG=.

3. Apply IF-THEN, ELSE IF-THEN conditions to assign CHECKFLG variable for each record: 0 for pass, . and 1 for fail condition.

4. Apply descending sort order on CHECKFLG variable to order 1 before 0 and '.' records by patient.

5. IF FIRST.SUBJID THEN OUTPUT to save first record per patient.

6. Report if any patient has CEHCKFLG = 1.

if &stdyvar = 1 then &visvar = 1;
    else if 10 <= &stdyvar <= 14 then &visvar=2;
    else if 26 <= &stdyvar <= 28 then &visvar=3;
    else if 34 <= &stdyvar <= 42 then &visvar=4;
    else if 54 <= &stdyvar <= 60 then &visvar=5;
    if &visvar = .  then put “War” “ning: Visit window error”;

 PROC FORMAT; VALUE VISITT 1 - 3 = 'VISIT 2'  5 - 10 = 'VISIT 5'   .='Missing' OTHER='Outside of Window'; QUIT;

proc sort data=OE(keep=siteID subjID usubjid visitNum visit visdat)
           out=oeSorted;
   by siteID SubjID visitNum;
run;

data dpquery_6;
   set oeSorted;
   by siteID SubjectID ;

   length prevDate  8 prevVisit $20 ;
   retain prevDate prevVisit ;

   if first.subjectID then do;
      prevVisit = visitName;
      prevDate  = visdat;
   end;
   else if visDat <= prevDate and cmiss(visDat,prevDate) = 0 then do;
   <...>
   end;
run;

 28. For indexed systems, use multiple SET statements with KEY=/UNIQUE option to use key variables to automatically and correctly join datasets.  Not that this technique keep all records from the first master dataset, without the KEY= option, such as that for IF A.

Four Components to the Model

A. SET master dataset with WHERE dataset option and w/o KEY=.  Note that master dataset must have defined all subsequent key variables in any following SET datasets for this technique to work.

B. SET datasets with KEY=

C. Just after the SET dataset to check, IF _IORC_ = %SYSRC(_DSENOM) THEN to capture non-matching records

D. ELSE DO to capture matching variables from other datasets for all records in master SET dataset

DATA CHECK;

 SET MALES2 (where=(sex='M'));

 SET CLASSID KEY=_PRIMKEY/UNIQUE;
  * First condition is to capture non matching records;
  * Second condition is to capture matching records;
  IF _IORC_ = %SYSRC(_DSENOM) THEN DO;
    _ERROR_=0;
    CHECK = 44;
    <...>
  END;
  ELSE DO;
  END;

 IF AGE <= 14;
 KEEP NAME AGE;
RUN;