Skip to content
Tutorials Center
My learning

Topics

Lesson 36 of 37 ยท Projects and Practice

Project: Clinical Data Validation

Build a repeatable data validation program in SAS with PROC FREQ, PROC MEANS, PROC SQL checks and a macro that logs issues to one data set, with runnable SQL and Python stand-ins.

SAS is widely used for clinical trials, surveys and regulated reporting, where data quality checks matter as much as the analysis. In this project you build a small validation program for a patient data set: find impossible values, missing required fields, duplicate subjects and inconsistent dates, and write every problem into one issues data set that a reviewer can read.

SAS is not available on this site; sas blocks are read-only and show no output. The stand-ins use standard SQL (SQLite) and Python to test the same checks on a small table. The data are invented for teaching.

The data

work.patients has one row per enrolled subject:

Variable Meaning
subject_id Unique id, required
sex M, F
age Years, expected 18 to 90
enroll_date, visit_date SAS dates; the visit cannot precede enrollment
weight_kg Expected 30 to 250

Design: one issues table

Rather than writing 20 separate reports, every check appends rows to the same structure: which subject, which rule, which value. A reviewer can then filter, count and export.

flowchart TD
    A["work.patients"] --> B["Check 1: required fields"]
    A --> C["Check 2: ranges"]
    A --> D["Check 3: duplicates"]
    A --> E["Check 4: date logic"]
    B --> F["work.issues"]
    C --> F
    D --> F
    E --> F
    F --> G["PROC FREQ by rule, PROC PRINT detail"]

Every check feeds one issues table.

Step 1: first look

Always start by exploring (see PROC FREQ and Frequency Tables and PROC MEANS and PROC SUMMARY):

SAS
proc freq data=work.patients;
  tables sex / missing;
run;

proc means data=work.patients n nmiss min max mean;
  var age weight_kg;
run;

missing shows blank sex values, and nmiss, min and max reveal impossible numbers like age 250 or weight 0 at a glance.

Step 2: a reusable check macro

SAS
data work.issues;
  length subject_id $8 rule $40 value $40;
  stop;                          /* an empty table with the right columns */
run;

%macro check(where=, rule=, valuevar=);
  proc sql;
    insert into work.issues
      select subject_id, "&rule", put(&valuevar, best12.)
        from work.patients
        where &where;
  quit;
%mend check;

%check(where=age < 18 or age > 90,           rule=Age out of range,   valuevar=age)
%check(where=weight_kg < 30 or weight_kg > 250, rule=Weight out of range, valuevar=weight_kg)
%check(where=missing(sex),                   rule=Sex missing,         valuevar=age)
%check(where=visit_date < enroll_date,       rule=Visit before enrollment, valuevar=visit_date)

Each call inserts the offending rows. Because where is a macro parameter, a single piece of code covers any rule you can express as a condition. The value column keeps the evidence; mixing a character and numeric value works because of put(). (The "Sex missing" call passes age only to fill the column, which is a small shortcut you could improve by a dedicated parameter.) Parameters, %local and where= text were covered in Macro Programs and Parameters.

Duplicates need a different pattern:

SAS
proc sql;
  insert into work.issues
    select subject_id, "Duplicate subject_id", put(count(*), 3.)
      from work.patients
      group by subject_id
      having count(*) > 1;
quit;

Stand-in: the checks in SQL

SQL Stand-in: write every problem to an issues table

Press Run (or Ctrl + Enter) to see the result here.

The first query is the equivalent of proc freq data=work.issues; tables rule; run;: a one-screen summary by rule. The second is the detail list for reviewers.

Step 3: summary and detail reports

SAS
title "Validation summary";
proc freq data=work.issues order=freq;
  tables rule / nocum;
run;

title "Validation detail";
proc sort data=work.issues; by subject_id rule; run;
proc print data=work.issues noobs;
run;
title;

If work.issues has zero rows, print a message instead of an empty report. In a macro:

SAS
proc sql noprint;
  select count(*) into :n_issues trimmed from work.issues;
quit;

%if &n_issues = 0 %then %put NOTE: all checks passed.;
%else %put WARNING: &n_issues issues found.;

Writing WARNING: at the start of a %put makes the line show in the log in warning color, which a scheduler or reviewer can scan for.

Stand-in: rules as data in Python

Real validation programs often keep their rules in a table so that non-programmers can review them. This stand-in does the same.

Python Stand-in: a rule table driving the checks

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Adding a rule means adding one line to the table, not writing a new step.

Good habits

  1. Never fix data silently. Log the issue, then correct it in a separate, documented step.
  2. Keep the original. Work on a copy; keep the raw data read-only.
  3. Count everything. Report N read, N flagged, N clean.
  4. Make it rerunnable. One program, no manual steps, same result each time.
  5. Review the log. Search for ERROR, WARNING, uninitialized, Missing values were generated and MERGE statement has more than one data set with repeats.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why collect all findings in one issues data set?

    A single structure (subject, rule, value) makes summaries and review simple.
  2. 2. Which check finds duplicate subject ids?

    Grouping by the id and keeping groups with more than one row exposes duplicates.
  3. 3. What should you do after finding a bad value?

    Good practice never changes data silently; every change is traceable.
  4. 4. Why start with PROC FREQ and PROC MEANS?

    Frequencies and min/max/nmiss are the fastest way to see data problems.

Summary

  • Validation programs write every finding to a single issues table with subject, rule and value.
  • A check macro taking a where= condition keeps each rule to one line.
  • PROC FREQ summarizes issues by rule; PROC PRINT lists the details.
  • Rules can live in a table so reviewers can read them.
  • Log, count, never fix silently, and make the program rerunnable.

Next: SAS Practice Exercises