Data Validation - QC/QA/Data Quality Techniques

Training Videos:

Applying SAS Program Validation Techniques

Below is a collection of validation strategies and SAS® papers on data validation techniques. See also CDM, PROC COMPARE, Six Sigma in SAS Business Intelligence and ETL, SAS Debugging, and Efficiency and Performance Survey. Mind map. For Quality Assurance, see also Metadata.

Quality Control

(by THOSE DOING the WORK on ALL WORK)

Quality Assurance

(by INDEPENDENT ON SAMPLE OF WORK)

Inherent in day to day work as part of a process to deliver a useful product

Self critiquing

Documented what went right, what went wrong and what you did about it

Steps may be updated based on QA results

An oversight function based on random or risk-based sample selection

Independent of the process

Will offer critical comment to challenge the quality of the system

Assures that the glue is in place to hold it all together

In general, the adaptive validation strategies below are suggestions only for large lab lists, tables and graphs. Most all other lists, tables or graphs such as demog, vitals, etc. require 100% parallel programming and checking 100% of all pages. Since the number of pages is expected to be less than 5 in general, this validation is not expected to be time consuming. In addition, all derived datasets are still 100% data values compared.

It is recommended to consult your statistician and FDA to assure appropriate sampling techniques are applied with acceptable type 1 and type 2 error rates. In general, while 100% parallel programming is required in over 80% of the cases, the number of page results that need to be validated may not require 100%. For the page numbers randomly selected, 100% of all values on these pages are compared.

If the random sample validation fails by finding a difference, then the production table is reproduced to correct the issue and a larger random sample size is required in the second pass. This sequence acceptance testing will increase the random sample size for each pass is followed if differences continue. If, however, there are no difference or issues, then subsequent passes are not required.

Risk Level	Adaptive Validation Strategies for lab lists, tables and graphs
LOW	Code Review, 10%-20% random sample pages

MEDIUM	25% to 100% random sample pages based on Sampling Techniques
1st pass	25% of random sample pages
2nd pass	50% of random sample pages if differences/issues found in 1st pass
3rd pass	75% of random sample pages if differences/issues found in 2nd pass
4th pass	100% of all pages if differenes/issues found in 3rd pass

HIGH	100% of all pages, Parallel Programming (Most time consuming)

Source	Validation Techniques 1. Specifications/Table Shells > 2. Source Program > 3. Output > 4. QC Program
Program	Program header, programming style and intermediate steps Validation excel file - program, log and output, See SAS Debugging Method - parallel programming against single source specification/table shells, code review and results spot check Macros - preventative programming and user message for each invalid parameter, keyword parameters with defaults, metadata checks and global macro variables, and document sections Code Review tips - identify incorrect logic or process flow, compliance with sops, modular, meet specifications
Datasets	PROC COMPARE datasets. Generally, 100% data values are compared. SDTM: Compare corresponding related variables, ex. PROC FREQ LBSTRESN*LBSTRESC Method - group related variables together, such as sequence of all date variables, to confirm consistency across variables when browsing dataset, use dataset shell macro to control order of variables Data issues - missing values, invalid values, zero and out of range
Listings	Other Listings, Datasets, PROC REPORT (detail lists), RTF Compare/QC Dataset from OUT= option. Confirm: specification with variable names, any subset conditions, correct units, description of Other or Comment from SUPPXX dataset, column alignments (left, center and right), case-sensitivity, sort order, message for missing records
Tables	Categorical data - PROC REPORT, PROC TABULATE and PROC FREQ Continuous data - PROC REPORT, PROC TABULATE, PROC MEANS and PROC SQL Other Tables such as complement Tables (Prior Con Meds and Con Meds), Listings, PROC TABULATE, RTF Compare/QC Dataset from OUT= option. For % calculations, make sure to delete duplicate records for the denominator variable with PROC SORT and NODUPKEY. Confirm: specification with variable names, any subset conditions, correct units, column alignments (left, center and right), case-sensitivity and sort order
Graphs	PROC TRANSPOSE Listings for raw, min and max data values, Tables, PROC SGPLOT, consistent y-axis scale within lab tests as needed for valid dose group comparisons, SAS Enterprise Guide Visual QC Steps: Text placement, Subset labels, titles, footnotes, source dataset compare, x and y axis,

Source

Validation Techniques

1. Specifications/Table Shells > 2. Source Program > 3. Output > 4. QC Program

Program

Program header, programming style and intermediate steps

Validation excel file - program, log and output, See SAS Debugging

Method - parallel programming against single source specification/table shells, code review and results spot check

Macros - preventative programming and user message for each invalid parameter, keyword parameters with defaults, metadata checks and global macro variables, and document sections

Code Review tips - identify incorrect logic or process flow, compliance with sops, modular, meet specifications

Datasets

PROC COMPARE datasets. Generally, 100% data values are compared.

SDTM: Compare corresponding related variables, ex. PROC FREQ LBSTRESN*LBSTRESC

Method - group related variables together, such as sequence of all date variables, to confirm consistency across variables when browsing dataset, use dataset shell macro to control order of variables

Data issues - missing values, invalid values, zero and out of range

Listings

Other Listings, Datasets, PROC REPORT (detail lists), RTF Compare/QC Dataset from OUT= option.

Confirm: specification with variable names, any subset conditions, correct units, description of Other or Comment from SUPPXX dataset, column alignments (left, center and right), case-sensitivity, sort order, message for missing records

Tables

Categorical data - PROC REPORT, PROC TABULATE and PROC FREQ

Continuous data - PROC REPORT, PROC TABULATE, PROC MEANS and PROC SQL

Other Tables such as complement Tables (Prior Con Meds and Con Meds), Listings, PROC TABULATE, RTF Compare/QC Dataset from OUT= option. For % calculations, make sure to delete duplicate records for the denominator variable with PROC SORT and NODUPKEY.

Confirm: specification with variable names, any subset conditions, correct units, column alignments (left, center and right), case-sensitivity and sort order

Graphs

PROC TRANSPOSE Listings for raw, min and max data values, Tables, PROC SGPLOT, consistent y-axis scale within lab tests as needed for valid dose group comparisons, SAS Enterprise Guide

Visual QC Steps: Text placement, Subset labels, titles, footnotes, source dataset compare, x and y axis,

SAS IQOQ SOP

Validation Plan The 5 Most Important Clinical SAS Programming Validation Steps, Brian Shilling [QC Checklist]

PROC COMPARE Validation: Let SAS do the comparison for you, Lara E.H. Guttadauro

Validating Listing Output: A Better Way, Hunter Vega, James Kniffen Jr

OPTIONS MPRINT MFILE; FILENAME MPRINT ‘T_XXX_CODE.SAS’; %MACRO WRAPPER; <%MAC> %MEND WRAPPER; %WRAPPER;

MPRINT/MFILE Have it both ways: Macros that produce publication-quality tables and stand-alone code, Linda Collins, Lisa Brooks, Michael Rea, Alan Hopkins

Macros Saving QC Time for Production Tables, Linfeng Xu, Sunil Gupta

RTF Processing Save Those Eyes: A Quality-Control Utility for Checking RTF Output Immediately
And Accurately, Michiel Hagendoorn, Jonathan Squire, Johnny Tai

RTF2DATA Utility A Utility Macro to convert RTF Table to SAS Dataset, Prove QC Quality — Create SAS® Dataset from RTF File

RTF_READ Decoding RTF Files

Regulatory Compliance (Validation Presentation)