How to solve a programming problem

Beginners start with code. Experienced clinical programmers start with a note. Then they test their assumptions on the data, and they write the derivation last. The derivation then has fewer surprises, and the QC programmer can follow every decision. This chapter shows the method on one real task: the same TRTEDT that chapter 15 found to be wrong.

16.1 The task

Derive TRTEDT, the date of last exposure to treatment, for ADSL.

16.2 Write a plain-language note before any code

Write four short lines. If you cannot fill one, you are not ready to code; ask first.

Part	For this task
Requirement	TRTEDT = the date of the last dose of study treatment
Source data	SDTM EX: one record per dosing interval, with EXSTDTC and EXENDTC
Rule	The end date of the last EX record of the subject
Open decision	What if that end date is missing? Ask the statistician before you code.

Then draw the flow. ASCII is enough:

sdtm.ex ──sort by start──► last record per subject ──► decision: which date? ──► TRTEDT
sdtm.dm ──────────────────► + RFENDTC
                                (trt_010)                  (trt_020)            (trt_final)

16.3 Test your assumptions with a check program

Every rule rests on assumptions about the data. Test them before you derive. A check program is short and read-only, and it answers yes or no questions:

16_1_check.sas

/* Chapter 16: a check program tests your assumptions before you derive */
PROC SQL;
  TITLE "Check 1: Randomised subjects without any EX record";
  SELECT COUNT(*) AS n_subjects
    FROM sdtm.dm
    WHERE armcd NE "Scrnfail" AND usubjid NOT IN (SELECT usubjid FROM sdtm.ex);

  TITLE "Check 2: EX records without an end date";
  SELECT COUNT(*) AS n_records
    FROM sdtm.ex
    WHERE MISSING(exendtc);

  TITLE "Check 3: EX records that end before they start";
  SELECT COUNT(*) AS n_records
    FROM sdtm.ex
    WHERE NOT MISSING(exendtc) AND exendtc < exstdtc;

  TITLE "Check 4: DM subjects with more than one record";
  SELECT COUNT(*) AS n_subjects
    FROM (SELECT usubjid FROM sdtm.dm GROUP BY usubjid HAVING COUNT(*) > 1);

  TITLE "Check 5: Subjects whose RFXSTDTC is not their first EX start";
  SELECT COUNT(*) AS n_subjects
    FROM sdtm.dm AS d,
         (SELECT usubjid, MIN(exstdtc) AS first_ex FROM sdtm.ex GROUP BY usubjid) AS e
    WHERE d.usubjid = e.usubjid AND d.rfxstdtc NE e.first_ex;
QUIT;
TITLE;

What you should see:

Check	Result
Check 1: Randomised subjects without any EX record	0
Check 2: EX records without an end date	6
Check 3: EX records that end before they start	0
Check 4: DM subjects with more than one record	0
Check 5: Subjects whose RFXSTDTC is not their first EX start	0

Four assumptions hold. One does not: 6 EX records have no end date. Without the check, you would find this only at QC time, or never. This is the moment to go back to your note and ask the statistician. Here the answer was: an open last dose record means that the subject was still dosed at the end of the study. So use the end of study date, RFENDTC from DM. This is the rule of section 9.3, and the reference ADSL uses it too.

Checks 1, 4 and 5 use a subquery: a SELECT inside another query, in NOT IN (SELECT ...) and in FROM (SELECT ...).

Exercise 16-1. Add check 6: EX records whose start date is partial (fewer than 10 characters).

16.4 Separate decisions from computation

Now write the derivation. Three habits make it easy to review:

1. A header with a change log. Purpose, input, output, the rule in words, and one line for each version.
2. Numbered steps with gaps. trt_010, trt_020, trt_030: each step reads the one before it. A new step later fits in as trt_015, and every intermediate data set can be opened and checked.
3. Decision data apart from computation. Step 020 decides which date to use and writes the reason (trtesrc). Step 030 only converts it. A reviewer reads all decisions in one place, and a later rule change touches one step.

16_2_derive.sas

/*******************************************************************************
  Program     : 16_2_derive.sas
  Purpose     : Date of last exposure to treatment (TRTEDT) for ADSL
  Input       : sdtm.ex, sdtm.dm
  Output      : work.trt_final (one record per dosed subject)
  Rule        : TRTEDT = end date of the last EX record. When that end date is
                missing, the subject was still dosed at the end of the study:
                use RFENDTC from DM (rule agreed with the statistician, the
                same rule as chapter 9).
  Change log  :
    2026-10-31  1.0  first version
    2026-10-31  1.1  decision data trt_020 keeps the source of each date
    2026-11-02  1.2  an open last record ends at RFENDTC, proof against ADSL
*******************************************************************************/

/* Step 010: the last EX record of each subject, with the end of study from DM */
PROC SORT DATA=sdtm.ex(KEEP=usubjid exseq exstdtc exendtc) OUT=trt_010;
  BY usubjid exstdtc exseq;
RUN;

PROC SORT DATA=sdtm.dm(KEEP=usubjid rfendtc) OUT=trt_dm;
  BY usubjid;
RUN;

DATA trt_010;
  MERGE trt_010(IN=inex) trt_dm;
  BY usubjid;
  IF inex AND LAST.usubjid;
RUN;

/* Step 020: decision data, which date and why */
DATA trt_020;
  SET trt_010;
  LENGTH trtedtc $10 trtesrc $7;
  IF NOT MISSING(exendtc) THEN DO;
    trtedtc = SUBSTR(exendtc, 1, 10);
    trtesrc = "EXENDTC";
  END;
  ELSE DO;
    trtedtc = SUBSTR(rfendtc, 1, 10);
    trtesrc = "RFENDTC";
  END;
RUN;

/* Step 030: computation only, no decisions */
DATA trt_final(KEEP=usubjid trtedt trtesrc);
  SET trt_020;
  trtedt = INPUT(trtedtc, ?? E8601DA.);
  FORMAT trtedt DATE9.;
RUN;

PROC FREQ DATA=trt_final;
  TABLES trtesrc / NOCUM;
RUN;

/* Proof: the same TRTEDT as the reference ADSL */
PROC SORT DATA=refadm.adsl(KEEP=usubjid trtedt) OUT=trt_ref;
  BY usubjid;
RUN;

PROC COMPARE BASE=trt_ref COMPARE=trt_final NOSUMMARY;
  ID usubjid;
  VAR trtedt;
RUN;
%PUT NOTE: PROC COMPARE result SYSINFO=&SYSINFO. (0 means equal);

What you should see: NOTE: The data set WORK.TRT_FINAL has 254 observations and 3 variables. and:

trtesrc	Frequency	Percent
EXENDTC	248	97.64
RFENDTC	6	2.36

and in the log:

NOTE: PROC COMPARE result SYSINFO=0 (0 means equal)

The frequency table is your proof that the rule ran as agreed: 6 subjects use the end of study date, exactly as the check program predicted. The compare is your proof that the rule is right: TRTEDT equals the reference ADSL for all 254 subjects.

See it in DataStep Studio

Open each numbered data set with a double-click in the Libraries tab while you write. Put the cursor on step 020 and press Ctrl+Alt+Up (Run To Line) to run everything up to it, then F12 to see the decision data. In the DATA step debugger, a breakpoint on the ELSE DO line with the condition MISSING(exendtc) stops only at the 6 records that use the fallback rule.

The DATA step debugger in step 020, stopped in the ELSE branch for 01-704-1233: EXENDTC is empty, so trtedtc takes RFENDTC (2013-07-14) and trtesrc = RFENDTC records why.

16.5 When documents disagree

The SAP, the ADaM specification and the table shells (the mock tables) sometimes say different things. Do not choose yourself, and do not code both versions “to be safe”. Write down the two readings and ask the statistician. Add the answer to your note and to the specification, so that the QC programmer and the next study find it.

Exercises

Exercise 16-2. The statistician changes the rule: a missing end date makes TRTEDT missing. Which step of 16_2_derive.sas changes, and which steps stay the same?

Exercise 16-3. Write the plain-language note for TRTSDT before you read the production program of chapter 15. Then compare.

Exercise 16-1 belongs to section 16.3. The answers are in Answers, Parts IV and V.