ADSL, the subject level data set

ADSL has one record per subject. Every other ADaM data set takes its treatment, population flags and key dates from ADSL, so an error here repeats everywhere. In this chapter you build the core of ADSL by hand from SDTM. Then you prove that it is the same as the reference ADSL of the pilot study. Last, you write your first study macro: a small tool for the one thing the program does three times.

9.0 Before you start: what SDTM looks like

ADaM is built from SDTM (the Study Data Tabulation Model), the standard form of the collected data. Each SDTM data set is a domain, and each domain has one record per something. Know what one record means before you count anything.

Domain	Records	One record per
DM (demographics)	306	subject, screen failures included (52 of them)
EX (exposure)	591	dosing interval
AE (adverse events)	1191	adverse event, for 225 subjects
VS (vital signs)	29643	measurement
SUPPDM	1197	extra value (qualifier) of a DM record

Four kinds of variables come back in many domains. Their first two letters are the domain, so AESEQ is the sequence number of AE and VSDTC is the date of VS:

• --SEQ numbers the records of a subject: subject 01-701-1015 has AE records with AESEQ 1, 2 and 3. Together with USUBJID, it is the key of a record. ADaM keeps it, so every analysis value can be traced back to its source record.
• --DTC is a date as text, in ISO 8601 form (2014-01-03). Text can hold a partial date, such as 2013-03, which a SAS date cannot. Chapter 11 shows what to do with partial dates.
• --DY is the study day: the number of days from the first dose. There is no day 0. The weight of subject 01-701-1015 has VSDY -7 at screening, 1 at baseline, 15 at week 2 and 29 at week 4.
• --TESTCD names what was measured, in the findings domains such as VS and LB (WEIGHT, SYSBP). One data set holds many tests.

SUPPDM holds the values that have no standard variable in DM. It is a tall data set: one record per subject and value, with the name of the value in QNAM and the value in QVAL. Subject 01-701-1015 has six records: COMPLT8, COMPLT16, COMPLT24, EFFICACY, ITT and SAFETY, each with QVAL = "Y". The pilot stores only the Y values: SAFETY and ITT have 254 records each, EFFICACY 234 and COMPLT24 118. A missing record means “no”.

ADSL needs these values as columns, one record per subject. So step 040 of the program turns the rows into columns with PROC TRANSPOSE (ID qnam; VAR qval;) and then sets the flag to N when a subject has no record. Chapter 7 explains PROC TRANSPOSE.

9.1 The plan: one numbered step per idea

Write ADSL as a chain of small steps. Each step reads the data set of the step before and adds one idea. The numbers have gaps (010, 020, 030) so that you can insert a step later without renaming the others. A QC programmer, or you in six months, can double-click any step in the Libraries tab and see what it added.

Step	Adds	Source
010	one record per randomised subject	DM
020	TRT01P, TRT01PN, TRT01A, TRT01AN: planned and actual treatment of period 01, text and number	DM ARM
030	TRTSDT, TRTEDT: date of the first and the last dose	EX
040	population flags, Y or N: SAFFL (safety), ITTFL (intent-to-treat), EFFFL (efficacy), COMP24FL (completed week 24)	SUPPDM
050	AGEGR1 (age group), HEIGHTBL, WEIGHTBL and BMIBL (height, weight and BMI at baseline)	DM, VS

9.2 The program

09_1_adsl.sas

/* Chapter 9: ADSL by hand, one numbered step per idea */

/* Step 010: one record per randomised subject */
DATA adsl_010;
  SET sdtm.dm(KEEP=studyid usubjid subjid siteid arm armcd age ageu sex race ethnic rfstdtc rfendtc);
  WHERE armcd NE "Scrnfail";
RUN;

/* Step 020: planned and actual treatment, text and number */
DATA adsl_020;
  LENGTH trt01p trt01a $20;
  SET adsl_010;
  trt01p = arm;
  IF      arm = "Placebo"              THEN trt01pn = 0;
  ELSE IF arm = "Xanomeline Low Dose"  THEN trt01pn = 54;
  ELSE IF arm = "Xanomeline High Dose" THEN trt01pn = 81;
  trt01a  = trt01p;
  trt01an = trt01pn;
RUN;

/* Step 030: first and last dose date from EX */
DATA ex_dt;
  SET sdtm.ex(KEEP=usubjid exstdtc exendtc);
  exstdt = INPUT(exstdtc, ?? E8601DA.);
  exendt = INPUT(exendtc, ?? E8601DA.);
RUN;

/* An EX record without an end date means: still dosed when the subject left */
PROC SQL;
  CREATE TABLE ex_first_last AS
    SELECT usubjid, MIN(exstdt) AS trtsdt FORMAT=DATE9., MAX(exendt) AS exlast FORMAT=DATE9.,
           SUM(MISSING(exendt)) AS n_open
      FROM ex_dt
      GROUP BY usubjid;
QUIT;

PROC SORT DATA=adsl_020; BY usubjid; RUN;

DATA adsl_030;
  MERGE adsl_020(IN=indm) ex_first_last;
  BY usubjid;
  IF indm;
  /* open last dose record: treatment ends at the end of the study */
  IF n_open > 0 THEN trtedt = INPUT(rfendtc, ?? E8601DA.);
  ELSE               trtedt = exlast;
  FORMAT trtedt DATE9.;
  DROP exlast n_open;
RUN;

/* Step 040: population flags from SUPPDM, N when no record */
PROC TRANSPOSE DATA=sdtm.suppdm OUT=supp(DROP=_name_ _label_);
  BY usubjid;
  ID qnam;
  VAR qval;
RUN;

DATA adsl_040;
  LENGTH saffl ittfl efffl comp24fl $1;
  MERGE adsl_030(IN=inadsl) supp(KEEP=usubjid safety itt efficacy complt24);
  BY usubjid;
  IF inadsl;
  IF safety   = "Y" THEN saffl    = "Y";
  ELSE                   saffl    = "N";
  IF itt      = "Y" THEN ittfl    = "Y";
  ELSE                   ittfl    = "N";
  IF efficacy = "Y" THEN efffl    = "Y";
  ELSE                   efffl    = "N";
  IF complt24 = "Y" THEN comp24fl = "Y";
  ELSE                   comp24fl = "N";
  DROP safety itt efficacy complt24;
RUN;

/* Step 050: age group and baseline BMI */
/* Weight has a baseline flag; height is measured once, at screening */
PROC SQL;
  CREATE TABLE vsbl_w AS
    SELECT usubjid,
           MAX(CASE WHEN vstestcd = "WEIGHT" AND vsblfl = "Y" THEN vsstresn END) AS weightbl,
           MAX(CASE WHEN vstestcd = "HEIGHT"                  THEN vsstresn END) AS heightbl
      FROM sdtm.vs
      WHERE vstestcd IN ("WEIGHT" "HEIGHT")
      GROUP BY usubjid;
QUIT;

DATA adsl;
  ATTRIB trt01p   LENGTH=$20 LABEL="Planned Treatment for Period 01"
         trt01pn             LABEL="Planned Treatment for Period 01 (N)"
         trt01a   LENGTH=$20 LABEL="Actual Treatment for Period 01"
         trtsdt              LABEL="Date of First Exposure to Treatment" FORMAT=DATE9.
         trtedt              LABEL="Date of Last Exposure to Treatment"  FORMAT=DATE9.
         agegr1   LENGTH=$5  LABEL="Pooled Age Group 1"
         saffl    LENGTH=$1  LABEL="Safety Population Flag"
         ittfl    LENGTH=$1  LABEL="Intent-To-Treat Population Flag"
         efffl    LENGTH=$1  LABEL="Efficacy Population Flag"
         comp24fl LENGTH=$1  LABEL="Completers of Week 24 Population Flag"
         heightbl            LABEL="Baseline Height (cm)"
         weightbl            LABEL="Baseline Weight (kg)"
         bmibl               LABEL="Baseline BMI (kg/m^2)";
  MERGE adsl_040(IN=inadsl) vsbl_w;
  BY usubjid;
  IF inadsl;
  IF      age < 65 THEN agegr1 = "<65";
  ELSE IF age > 80 THEN agegr1 = ">80";
  ELSE                  agegr1 = "65-80";
  /* the specification keeps one decimal */
  IF NOT MISSING(weightbl) THEN weightbl = ROUND(weightbl, 0.1);
  IF NOT MISSING(heightbl) THEN heightbl = ROUND(heightbl, 0.1);
  IF NOT MISSING(weightbl) AND NOT MISSING(heightbl) THEN bmibl = ROUND(weightbl / (heightbl / 100)**2, 0.1);
RUN;

/* Compare with the reference ADSL */
PROC COMPARE BASE=refadm.adsl COMPARE=adsl
             (KEEP=usubjid trt01p trt01pn trt01a trtsdt trtedt saffl ittfl efffl comp24fl agegr1 heightbl weightbl bmibl)
             CRITERION=1E-8 NOSUMMARY;
  ID usubjid;
  VAR trt01p trt01pn trt01a trtsdt trtedt saffl ittfl efffl comp24fl agegr1 heightbl weightbl bmibl;
RUN;
%PUT NOTE: PROC COMPARE result SYSINFO=&SYSINFO. (0 means equal);

What you should see: every step has 254 observations, from WORK.ADSL_010 to WORK.ADSL, and the log says:

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

SYSINFO=0 means that PROC COMPARE found no difference. It compared 13 variables for all 254 subjects: TRT01P, TRT01PN, TRT01A, TRTSDT, TRTEDT, SAFFL, ITTFL, EFFFL, COMP24FL, AGEGR1, HEIGHTBL, WEIGHTBL and BMIBL. Your ADSL and the reference ADSL agree.

Chapters 2 and 7 used PROC COMPARE without an explanation. Here are its parts:

• BASE= is the data set you trust (the reference), COMPARE= is yours.
• ID usubjid; pairs the records by subject, not by row number. Both data sets must be sorted by the ID variables.
• VAR lists the variables to compare. Without it, PROC COMPARE compares every variable with the same name.
• CRITERION=1E-8 treats numbers that differ by less than 0.00000001 as equal, so rounding noise is not a difference. NOSUMMARY leaves out the summary pages, so the output shows only the differences.
• After the step, &SYSINFO. holds the result as a sum of codes. 0 means equal. 32 means a variable has a different label. 64 means a record of BASE= has no partner. 128 means a record of COMPARE= has no partner. 4096 means at least one value differs. So 4224 (4096 + 128) means both.

After F4 on 09_1_adsl.sas: the Libraries tab lists every numbered step, ADSL_010 to ADSL_040 and the final ADSL, each with 254 rows. The log ends with PROC COMPARE result SYSINFO=0: equal to the CDISC reference.

Exercise 9-1. Add TRTDURD, the duration of treatment in days: trtedt - trtsdt + 1. Compare it with TRTDUR in the reference ADSL.

9.3 Three lessons from the first runs

The program above is the fourth version. The first three did not match the reference, and each difference taught a rule that every ADSL needs.

Height has no baseline flag

The first version took height and weight from the records with VSBLFL = "Y". The log said ERROR: Variable height is not on file WORK.VSBL_W. In the pilot, height is measured once, at screening. That record has no baseline flag. Read each parameter from the record the specification names, not from one rule for all.

The specification rounds

The second version found differences of a few hundredths in every height and weight: 147.32 against 147.3. The reference keeps one decimal.

When PROC COMPARE shows many small differences in one direction, look for a rounding rule before you look for a logic error. Round only when a value exists: ROUND of a missing value writes a NOTE that a log check flags.

An open dose record

The third version found 6 subjects with a different TRTEDT. Look at the data before you change a rule:

09_2_adsl_check.sas

/* Chapter 9: look at the data before you change a rule */
PROC SQL;
  SELECT usubjid, exseq, extrt, exstdtc, exendtc
    FROM sdtm.ex
    WHERE usubjid IN ("01-704-1233" "01-705-1018");
QUIT;

What you should see:

USUBJID	EXSEQ	EXTRT	EXSTDTC	EXENDTC
01-704-1233	1	PLACEBO	2013-03-21	2013-04-04
01-704-1233	2	PLACEBO	2013-04-05
01-705-1018	1	PLACEBO	2013-07-05

An EX record without an end date is an open dose record. It means that the subject was still taking the study drug when the subject left the study. MAX(exendt) ignores the missing value. It returns the end of an earlier record (04APR2013), or nothing at all. The reference ADSL uses the end of study date, RFENDTC from DM, instead: 14JUL2013 for subject 01-704-1233.

This is the book’s rule for TRTEDT, and step 030 does it:

• TRTEDT is the latest EX end date of the subject.
• When the subject has an open dose record, TRTEDT is RFENDTC, the end of the study.

The rule changes 6 subjects of the pilot: 01-704-1233, 01-705-1018, 01-705-1031, 01-705-1303, 01-705-1377 and 01-705-1382. With it, PROC COMPARE gives SYSINFO=0 against the reference. You meet these 6 subjects twice more, from two other sides. Chapter 15 finds them in QC, when a simpler rule is compared with the reference. Chapter 16 finds them with a check program before any derivation, and the statistician gives the same rule.

Caution

MIN, MAX and SUM skip missing values without a NOTE. When a missing value means something, as an open dose record does, count it yourself (SUM(MISSING(exendt))) and decide what it means.

Exercise 9-2. Change step 030 back to trtedt = exlast; for every subject and run the comparison. Which subjects differ, and what does PROC COMPARE show for them?

9.4 See it in DataStep Studio

See it in DataStep Studio

Run 09_1_adsl.sas with F4. In the Libraries tab you see ADSL_010 to ADSL, each with 254 rows. Double-click ADSL_030 and type usubjid = "01-704-1233" in the WHERE box: TRTEDT is 14JUL2013. To watch the open-dose rule decide, put the cursor in the DATA adsl_030 step and click Debug DATA Step. Click in the margin of the IF n_open > 0 line to set a breakpoint. Right-click it, choose Edit Breakpoint and type the condition usubjid = "01-704-1233". Press F5: the Variables view shows n_open = 1. Press F10 twice (the part after THEN is a statement of its own): trtedt becomes 14JUL2013.

The DATA step debugger in ADSL_030, stopped for subject 01-704-1233 (condition usubjid = “01-704-1233”): n_open = 1, so the last dose record has no end date and TRTEDT comes from RFENDTC.

9.5 Write the tool when you repeat yourself

Look at 09_1_adsl.sas again. Three times it turns an ISO 8601 text date into a SAS date: exstdtc, exendtc and rfendtc. Chapter 11 needs it again for adverse events, and there some dates have no day. When the same code comes back a third time, write it once as a macro and test the macro. This is a study macro: a small tool for this study, written by you, with a header that says what it does.

09_3_m_iso_dt.sas

/* EXPECT: ERROR [M_ISO_DT] */
/* Chapter 9: a study macro for ISO 8601 dates, written because 09_1 does the same thing three times (needs 09_1) */

/*******************************************************************************
  Macro       : %m_iso_dt
  Version     : 1.0
  Purpose     : Turn an ISO 8601 text date (--DTC) into a SAS date.
                With impute=FIRST or impute=LAST a date without a day
                (2013-03) gets the first or the last day of the month, and
                the flag variable says D (the day was imputed). A date with
                the year only stays missing.
  Parameters  :
    data=     input data set, data set options allowed (required)
    out=      output data set (required)
    dtc=      the ISO 8601 text variable, e.g. aestdtc (required)
    dt=       the new SAS date variable, e.g. astdt (required)
    impute=   NONE (default), FIRST or LAST
    flag=     the new imputation flag variable, e.g. astdtf (required
              when impute is FIRST or LAST)
  Preconditions  : &dtc. exists in &data.
  Postconditions : &out. = &data. plus &dt. (FORMAT DATE9.) and &flag.
  Change log  :
    2026-11-01  1.0  first version
*******************************************************************************/
%MACRO m_iso_dt(data=, out=, dtc=, dt=, impute=NONE, flag=);
  %LOCAL macro p ds dsid hasvar rc;
  %LET macro  = M_ISO_DT;
  %LET impute = %UPCASE(&impute.);

  /* Check the parameters: nothing runs when one check fails */
  %DO p = 1 %TO 4;
    %LET ds = %SCAN(data out dtc dt, &p.);
    %IF %SUPERQ(&ds.) = %THEN %DO;
      %PUT %STR(ERR)OR: [&macro.] the parameter &ds.= is required.;
      %RETURN;
    %END;
  %END;
  /* the data set name without its options: sdtm.ex(KEEP=...) is sdtm.ex */
  %LET ds = %SCAN(&data., 1, %STR(%());
  %IF NOT %SYSFUNC(EXIST(&ds.)) %THEN %DO;
    %PUT %STR(ERR)OR: [&macro.] the data set &ds. does not exist.;
    %RETURN;
  %END;
  %LET dsid   = %SYSFUNC(OPEN(&ds.));
  %LET hasvar = %SYSFUNC(VARNUM(&dsid., &dtc.));
  %LET rc     = %SYSFUNC(CLOSE(&dsid.));
  %IF &hasvar. = 0 %THEN %DO;
    %PUT %STR(ERR)OR: [&macro.] the variable &dtc. is not in &ds..;
    %RETURN;
  %END;
  %IF NOT (&impute. = NONE OR &impute. = FIRST OR &impute. = LAST) %THEN %DO;
    %PUT %STR(ERR)OR: [&macro.] impute=&impute. is not NONE, FIRST or LAST.;
    %RETURN;
  %END;
  %IF &impute. NE NONE AND %SUPERQ(flag) = %THEN %DO;
    %PUT %STR(ERR)OR: [&macro.] impute=&impute. needs flag=, the imputation flag variable.;
    %RETURN;
  %END;

  /* The mechanism: the work variables have a private prefix, so they cannot */
  /* overwrite a variable of the caller                                       */
  DATA &out.;
    SET &data.;
    %IF &impute. NE NONE %THEN %DO;
      LENGTH &flag. $1;
      &flag. = "";
    %END;
    _mid_yy = INPUT(SUBSTR(&dtc., 1, 4), ?? 4.);
    _mid_mm = .;
    IF LENGTHN(&dtc.) >= 7 THEN _mid_mm = INPUT(SUBSTR(&dtc., 6, 2), ?? 2.);
    /* a complete date, perhaps with a time part: 2013-03-21T10:30 */
    IF LENGTHN(&dtc.) >= 10 THEN &dt. = INPUT(SUBSTR(&dtc., 1, 10), ?? E8601DA.);
    %IF &impute. = FIRST %THEN %DO;
      ELSE IF NOT MISSING(_mid_mm) THEN DO;
        &dt.   = MDY(_mid_mm, 1, _mid_yy);
        &flag. = "D";
      END;
    %END;
    %ELSE %IF &impute. = LAST %THEN %DO;
      ELSE IF NOT MISSING(_mid_mm) THEN DO;
        &dt.   = INTNX("MONTH", MDY(_mid_mm, 1, _mid_yy), 0, "END");
        &flag. = "D";
      END;
    %END;
    ELSE &dt. = .;
    FORMAT &dt. DATE9.;
    DROP _mid_yy _mid_mm;
  RUN;
%MEND m_iso_dt;

/* Use it: the dose dates of 09_1 again, now with the macro */
%m_iso_dt(data=sdtm.ex(KEEP=usubjid exstdtc exendtc), out=ex_m010, dtc=exstdtc, dt=exstdt)
%m_iso_dt(data=ex_m010, out=ex_m020, dtc=exendtc, dt=exendt)

/* the same records in the same order as WORK.EX_DT of 09_1 */
PROC COMPARE BASE=ex_dt COMPARE=ex_m020 NOSUMMARY;
  VAR exstdt exendt;
RUN;
%PUT NOTE: PROC COMPARE result SYSINFO=&SYSINFO. (0 means equal);

/* Test it on the cases the study data does not have yet */
DATA iso_cases;
  LENGTH dtc $16;
  INPUT dtc $ exp_first :DATE9. exp_last :DATE9.;
  IF dtc = "-" THEN dtc = "";
  FORMAT exp_first exp_last DATE9.;
  DATALINES;
2013-03-21        21MAR2013 21MAR2013
2013-03-21T10:30  21MAR2013 21MAR2013
2013-03           01MAR2013 31MAR2013
2012-02           01FEB2012 29FEB2012
2013              .         .
-                 .         .
;
RUN;

%m_iso_dt(data=iso_cases, out=iso_first, dtc=dtc, dt=dt, impute=FIRST, flag=dtf)
%m_iso_dt(data=iso_cases, out=iso_last,  dtc=dtc, dt=dt, impute=LAST,  flag=dtf)

DATA iso_test;
  MERGE iso_first(RENAME=(dt=dt_first dtf=dtf_first))
        iso_last(RENAME=(dt=dt_last dtf=dtf_last));
  LENGTH result $4;
  IF dt_first = exp_first AND dt_last = exp_last THEN result = "pass";
  ELSE                                                result = "FAIL";
RUN;

PROC PRINT DATA=iso_test NOOBS;
  VAR dtc dt_first dtf_first dt_last dtf_last result;
RUN;

/* A wrong call stops before any step runs: here the name of the variable is wrong */
%m_iso_dt(data=sdtm.ex, out=ex_bad, dtc=exstdt, dt=exstdt_n)

Read the macro from the top:

1. The header says what the macro does, what it needs (preconditions) and what it gives (postconditions). A reader does not have to read the code to use it.

2. The parameter checks come first. Each check looks for one wrong call: a required parameter that is empty, an input that does not exist, a variable that is not there, or an impute= value that is not allowed. A wrong call writes ERROR: [M_ISO_DT] ... and stops before any step runs. Three tools are new here:

   • %RETURN ends the macro at once, like %GOTO to the %MEND.
   • ds holds a name, such as data. %SUPERQ(&ds.) then gives the value of data. It does not resolve & or % in that value. The loop puts the names data, out, dtc and dt in ds one after the other, so one %IF checks four parameters.
   • %SYSFUNC(OPEN(...)), VARNUM and CLOSE look into a data set without a DATA step. OPEN returns a number for the open data set, or 0 if it cannot open it. VARNUM returns the position of a variable, or 0 if the variable is not there. CLOSE releases the data set. Always close what you open.

   %SCAN(&data., 1, %STR(%()) removes data set options, so that sdtm.ex(KEEP=...) is checked as sdtm.ex.

3. The mechanism is one DATA step. The work variables have the prefix _mid_, so they cannot overwrite a variable of the program that calls the macro. %IF writes only the code the call needs: with impute=NONE there is no imputation code in the step at all.

4. The test comes with the macro. First the program rebuilds the dose dates of 09_1_adsl.sas and compares them. Then it runs six cases. One is a complete date. Five are cases that the study data does not have yet: a time part, a missing day, a leap year, a year only and an empty date.

What you should see:

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

and the test table, every row pass:

dtc	dt_first	dtf_first	dt_last	dtf_last	result
2013-03-21	21MAR2013		21MAR2013		pass
2013-03-21T10:30	21MAR2013		21MAR2013		pass
2013-03	01MAR2013	D	31MAR2013	D	pass
2012-02	01FEB2012	D	29FEB2012	D	pass
2013	.		.		pass
(empty)	.		.		pass

The last call is wrong on purpose: dtc=exstdt names a variable that EX does not have. The log says

ERROR: [M_ISO_DT] the variable exstdt is not in sdtm.ex.

and no step runs. A macro that stops with a clear message is easier to use than one that runs and gives a wrong date.

The macro debugger in the last call of %m_iso_dt, stopped at %IF &hasvar. = 0: dtc = exstdt, and hasvar = 0 because SDTM.EX has no variable EXSTDT. The next line writes the ERROR and stops the macro.

Your company has a macro for this

Most companies have a library macro that converts dates. Use it at work. Write this one once anyway. When you have written the parameter checks, the private work variables and the test cases yourself, you can read any library macro and judge it.

Exercise 9-3. Step 040 of 09_1_adsl.sas repeats one pattern four times: IF x = "Y" THEN flag = "Y"; ELSE flag = "N";. Write %m_yn(data=, out=, var=, flag=) and use it four times. Prove with PROC COMPARE that ADSL does not change.

Exercise 9-4. Add the case 2013-12 to iso_cases. What do impute=FIRST and impute=LAST give? Which SAS function finds the last day of a month for you?

Exercises

The exercises are next to the program they change: 9-1 and 9-2 change 09_1_adsl.sas, 9-3 and 9-4 change 09_3_m_iso_dt.sas. The answers are in Answers, Part II.