BDS data sets, ADVS step by step

Most analysis data sets of a study have the basic data structure (BDS): one record per subject, parameter and analysis time point. Vital signs, laboratory values and questionnaires all use it. The same five derivations come back in every BDS data set, so learn them once, here, on body weight.

Variable	Meaning
`PARAMCD`, `PARAM`	which measurement: `WEIGHT`, “Weight (kg)”
`AVAL`, `ADT`, `ADY`	the value, its date and the study day
`AVISIT`, `AVISITN`	the analysis visit
`ABLFL`, `BASE`	the baseline record, and its value on every record of the subject
`CHG`, `PCHG`	change and percent change from baseline
`ANL01FL`	the one record per visit that the analysis uses

10.1 ADVS by hand

The program reads WORK.ADSL from Chapter 9, so run 09_1_adsl.sas first, in the same session.

/* Chapter 10: ADVS for body weight by hand (needs ADSL from chapter 9) */

/* Step 010: one record per weight measurement, with a date and a study day */
DATA advs_010;
  LENGTH paramcd $8 param $40;
  MERGE sdtm.vs(IN=invs WHERE=(vstestcd = "WEIGHT")
                KEEP=usubjid vsseq vstestcd visitnum visit vsdtc vsstresn)
        adsl(IN=inadsl KEEP=usubjid trtsdt trt01p trt01pn);
  BY usubjid;
  IF invs AND inadsl;
  paramcd = "WEIGHT";
  param   = "Weight (kg)";
  aval    = vsstresn;
  adt     = INPUT(vsdtc, ?? E8601DA.);
  /* no day 0: the first day of treatment is day 1, the day before is day -1 */
  IF      adt >= trtsdt        THEN ady = adt - trtsdt + 1;
  ELSE IF NOT MISSING(adt)     THEN ady = adt - trtsdt;
  FORMAT adt DATE9.;
RUN;

/* Step 020: the analysis visit is the visit as collected (nominal visit) */
DATA advs_020;
  LENGTH avisit $20 ablfl $1;
  SET advs_010;
  IF visit = "BASELINE" THEN DO;
    avisit  = "Baseline";
    avisitn = 0;
    ablfl   = "Y";
  END;
  ELSE IF SCAN(visit, 1) = "WEEK" THEN DO;
    avisitn = INPUT(SCAN(visit, 2), ?? 8.);
    avisit  = CATX(" ", "Week", avisitn);
    ablfl   = "";
  END;
  ELSE ablfl = "";
RUN;

/* Step 030: BASE on every record of the subject, then CHG and PCHG */
PROC SQL;
  CREATE TABLE advs_030 AS
    SELECT a.*, b.base
      FROM advs_020 AS a LEFT JOIN
           (SELECT usubjid, aval AS base FROM advs_020 WHERE ablfl = "Y") AS b
           ON a.usubjid = b.usubjid
      ORDER BY usubjid, adt, vsseq;
QUIT;

DATA advs_040;
  SET advs_030;
  IF NOT MISSING(aval) AND NOT MISSING(base) THEN DO;
    chg = aval - base;
    IF base NE 0 THEN pchg = chg / base * 100;
  END;
RUN;

/* Step 050: one analysis record per visit (the last one if a visit has two) */
PROC SORT DATA=advs_040; BY usubjid avisitn adt vsseq; RUN;

DATA advs;
  SET advs_040;
  BY usubjid avisitn;
  LENGTH anl01fl $1;
  IF NOT MISSING(avisitn) AND LAST.avisitn THEN anl01fl = "Y";
  ELSE                                           anl01fl = "";
RUN;

PROC SORT DATA=advs; BY usubjid adt vsseq; RUN;

/* Compare the analysis records with the reference ADVS */
DATA ref_wt;
  SET refadm.advs(WHERE=(paramcd = "WEIGHT" AND anl01fl = "Y" AND 0 <= avisitn <= 26));
RUN;
PROC SORT DATA=ref_wt; BY usubjid avisitn; RUN;

DATA our_wt;
  SET advs(WHERE=(anl01fl = "Y"));
RUN;
PROC SORT DATA=our_wt; BY usubjid avisitn; RUN;

PROC COMPARE BASE=ref_wt COMPARE=our_wt CRITERION=1E-8 NOSUMMARY;
  ID usubjid avisitn;
  VAR adt ady aval base chg ablfl;
RUN;
%PUT NOTE: PROC COMPARE result SYSINFO=&SYSINFO. (0 means equal);

What you should see: WORK.ADVS has 2050 observations, one per weight measurement in SDTM VS. The comparison uses the 1795 analysis records of the reference ADVS from Baseline to Week 26, and the log says:

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

For all 1795 records, ADT, ADY, AVAL, BASE, CHG and ABLFL are the same as in the reference.

Study day has no day 0

ADY counts the first day of treatment as day 1. The day before it is day -1, not day 0. That is why step 010 adds 1 only for dates on or after TRTSDT. For subject 01-701-1015, the Week 2 weight was measured on 16JAN2014, two weeks after the first dose on 02JAN2014: ADY = 15.

BASE belongs to every record

BASE is the value of the baseline record, repeated on every record of the subject. Step 030 does this with a join: the subquery picks the baseline value of each subject, and the LEFT JOIN puts it on all records. A join is the clearest way to “copy one value to the whole group”.

One analysis record per visit

A subject can have two weights at one visit (a repeated measurement). ANL01FL = "Y" marks the one the analysis uses. The rule here: the last one. Your SAP may say “the closest to the planned day” or “the average”; the flag is where that rule lives.

WORK.ADVS in the data viewer with WHERE usubjid = “01-701-1015”: 11 of 2050 records. A screening record without an analysis visit, Baseline with ablfl = “Y”, then Week 2 to Week 26.

Exercise 10-1. Change step 050 so that ANL01FL marks the first record of a visit instead of the last. Before you run it, predict: how many records change their flag? Then count the visits that have more than one weight.

10.2 Blood pressure: the same derivations again, so write the tools

Blood pressure and pulse need the same derivations as weight, with two differences. First, the pilot measures them in three positions, and each position has its own baseline. The positions are ATPTN 815 (lying 5 minutes), 816 (standing 1 minute) and 817 (standing 3 minutes). Second, this time the baseline follows the SAP, not the name of the visit. The SAP text goes at the top of the program, next to the code that does it:

/*****************************************************************************/
/* Example SAP definition: Baseline                                          */
/* Baseline is the last non-missing value on or before the day of the first  */
/* dose. Blood pressure and pulse are measured in three positions: each      */
/* position has its own baseline.                                            */
/*****************************************************************************/

Animation The baseline rule on a timeline · 39 s, no sound

Every BDS data set of a study needs this baseline rule, and then BASE, CHG and PCHG. Steps 020 to 040 of 10_1_advs.sas would come back with one more key. Write the two jobs once, as two study macros in one file:

/* Chapter 10: the study macros for BDS data sets, %m_baseline and %m_chg. Run this file once, then 10_2 and later programs use them. */

/*******************************************************************************
  Macro            : %m_baseline (study macro)
  Version          : 2.0
  Purpose          : Mark the baseline record of each group, as the SAP
                     defines it: the last record that meets the condition.
  SAP text         : example SAP definition written for this book
                     "Baseline is the last non-missing value on or before the
                     day of the first dose. When that value comes from a
                     screening visit, the screening value is the baseline.
                     Blood pressure and pulse are measured in three positions:
                     each position has its own baseline."
  Parameters       :
    data=            a BDS data set (required)
    out=             the output data set, sorted by &sortby. (required)
    by=              one baseline per group (default usubjid paramcd)
    where=           which records can be baseline, a WHERE condition
                     (default adt <= trtsdt AND NOT MISSING(aval))
    sortby=          the by= variables, then the time order: the last record
                     of a group is its baseline (default &by. adt). Add a
                     key such as the SDTM sequence number when two records
                     of a group can have the same date.
    baseline_visit=  AVISITN of the new records when copy=Y (default 0)
    copy=            N: set ABLFL = "Y" on the collected baseline record
                     (default; the reference ADVS of the pilot does this)
                     Y: also add a copy with AVISIT "Baseline", AVISITN =
                     &baseline_visit., ABLFL "Y". Only for records without an
                     analysis visit, else a group has two Baseline rows
  Preconditions    : &data. has the variables of by=, sortby= and where=;
                     sortby= starts with the variables of by=
  Postconditions   : &out. has all records of &data. and the variable ABLFL,
                     "Y" on exactly one record per group that has a record
                     meeting where=; with copy=Y one more record per such
                     group; work data sets _mbl_: are deleted
  Validation level : independent double programming of the baseline values
  Author           : your name, date
  Change history   :
    2026-11-01  1.0  first version, case study of Part V
    2026-11-01  1.1  parameters renamed to the book's interface: data= (was
                     input=), where= (was baseline_cond=), by= (was groupby=),
                     out= (was output=)
    2026-11-01  2.0  moved to chapter 10 for every BDS data set: defaults for
                     by=, where= and sortby=; copy=N flags the record, copy=Y
                     adds a baseline record; out= has all records
*******************************************************************************/
%MACRO m_baseline(data=, out=, by=usubjid paramcd, where=adt <= trtsdt AND NOT MISSING(aval),
                  sortby=&by. adt, baseline_visit=0, copy=N)
  / DES = 'Mark the baseline record of each group';
  %LOCAL macro mversion i p n last;
  %LET macro    = %UPCASE(&SYSMACRONAME.);
  %LET mversion = 2.0;

  /* Check the parameters: required ones, the input, the sort order, copy= */
  %DO i = 1 %TO 5;
    %LET p = %SCAN(data out by where sortby, &i.);
    %IF %LENGTH(%SUPERQ(&p.)) = 0 %THEN %DO;
      %PUT %STR(ERR)OR: [&macro.] Parameter %UPCASE(&p.)= is required.;
      %RETURN;
    %END;
  %END;
  %IF NOT %SYSFUNC(EXIST(&data.)) %THEN %DO;
    %PUT %STR(ERR)OR: [&macro.] Data set &data. does not exist.;
    %RETURN;
  %END;
  %LET n = %SYSFUNC(COUNTW(&by., %STR( )));
  %DO i = 1 %TO &n.;
    %IF %UPCASE(%SCAN(&by., &i., %STR( ))) NE %UPCASE(%SCAN(&sortby., &i., %STR( ))) %THEN %DO;
      %PUT %STR(ERR)OR: [&macro.] SORTBY= must start with the BY= variables.;
      %RETURN;
    %END;
  %END;
  %LET copy = %UPCASE(&copy.);
  %IF &copy. NE N AND &copy. NE Y %THEN %DO;
    %PUT %STR(ERR)OR: [&macro.] COPY= must be N or Y, not &copy..;
    %RETURN;
  %END;
  %LET last = %SCAN(&by., -1, %STR( ));

  %PUT NOTE: [&macro.] Version &mversion. started %SYSFUNC(DATE(), DATE9.) %SYSFUNC(TIME(), HHMM.);

  /* Step 010: the records in time order within each group, numbered */
  PROC SORT DATA=&data. OUT=_mbl_010;
    BY &sortby.;
  RUN;
  DATA _mbl_010;
    SET _mbl_010;
    _mbl_n = _N_;
  RUN;

  /* Step 020: the number of the last record of each group that can be baseline */
  DATA _mbl_020(KEEP=_mbl_n);
    SET _mbl_010(WHERE=(&where.));
    BY &by.;
    IF LAST.&last.;
  RUN;

  /* Step 030: flag that record, or add a copy of it */
  DATA &out.(DROP=_mbl_n);
    LENGTH ablfl $1;
    MERGE _mbl_010 _mbl_020(IN=_mbl_bl);
    BY _mbl_n;
    %IF &copy. = N %THEN %DO;
      IF _mbl_bl THEN ablfl = "Y";
      ELSE            ablfl = "";
    %END;
    %ELSE %DO;
      ablfl = "";
      OUTPUT;
      IF _mbl_bl THEN DO;
        avisit  = "Baseline";
        avisitn = &baseline_visit.;
        ablfl   = "Y";
        OUTPUT;
      END;
    %END;
  RUN;

  /* Remove the work data sets of this macro */
  PROC DATASETS LIBRARY=work NOLIST NOWARN;
    DELETE _mbl_:;
  RUN; QUIT;
%MEND m_baseline;

/*******************************************************************************
  Macro            : %m_chg (study macro)
  Version          : 1.1
  Purpose          : Put the baseline value on every record of its group (BASE)
                     and compute the change (CHG) and the percent change (PCHG).
  Parameters       :
    data=            input data set with one baseline record per group (required)
    out=             output data set, sorted by &by. (required)
    by=              the variables of one group, e.g. usubjid paramcd (required)
    flag=            the baseline flag, Y on the baseline record (default ablfl)
  Preconditions    : &data. has the analysis value aval; at most one record
                     with &flag. = "Y" per group
  Postconditions   : &out. = &data. plus the new variables base, chg and
                     pchg (existing variables with these names are replaced).
                     CHG is computed on every record with AVAL and BASE: on
                     the baseline record it is 0, before baseline it is not
                     empty. Your specification may ask for empty values there.
  Validation level : independent double programming of BASE and CHG
  Author           : your name, date
  Change history   :
    2026-11-01  1.0  first version
    2026-11-01  1.1  version NOTE and messages as in %m_baseline; WORK._MCH_021
                     keeps the whole records of a group with two baselines
*******************************************************************************/
%MACRO m_chg(data=, out=, by=, flag=ablfl)
  / DES = 'BASE, CHG and PCHG from the baseline record of each group';
  %LOCAL macro mversion i p last ndup;
  %LET macro    = %UPCASE(&SYSMACRONAME.);
  %LET mversion = 1.1;

  /* Check the parameters: required ones and the input */
  %DO i = 1 %TO 3;
    %LET p = %SCAN(data out by, &i.);
    %IF %LENGTH(%SUPERQ(&p.)) = 0 %THEN %DO;
      %PUT %STR(ERR)OR: [&macro.] Parameter %UPCASE(&p.)= is required.;
      %RETURN;
    %END;
  %END;
  %IF NOT %SYSFUNC(EXIST(&data.)) %THEN %DO;
    %PUT %STR(ERR)OR: [&macro.] Data set &data. does not exist.;
    %RETURN;
  %END;
  %LET last = %SCAN(&by., -1, %STR( ));

  %PUT NOTE: [&macro.] Version &mversion. started %SYSFUNC(DATE(), DATE9.) %SYSFUNC(TIME(), HHMM.);

  /* Step 010: the records in group order */
  PROC SORT DATA=&data. OUT=_mch_010;
    BY &by.;
  RUN;

  /* Step 020: one baseline value per group; two baseline records is an error */
  DATA _mch_020(KEEP=&by. base) _mch_021;
    SET _mch_010(WHERE=(&flag. = "Y"));
    BY &by.;
    base = aval;
    IF FIRST.&last. AND LAST.&last. THEN OUTPUT _mch_020;
    ELSE                                  OUTPUT _mch_021;
  RUN;
  PROC SQL NOPRINT;
    SELECT COUNT(*) INTO :ndup TRIMMED FROM _mch_021;
  QUIT;
  %IF &ndup. > 0 %THEN %DO;
    %PUT %STR(ERR)OR: [&macro.] &ndup. baseline records in groups with more than one. See WORK._MCH_021.;
    %RETURN;
  %END;

  /* Step 030: BASE on every record of the group, then CHG and PCHG */
  DATA &out.;
    MERGE _mch_010 _mch_020;
    BY &by.;
    IF NOT MISSING(aval) AND NOT MISSING(base) THEN DO;
      chg = aval - base;
      IF base NE 0 THEN pchg = chg / base * 100;
    END;
  RUN;

  /* Remove the work data sets of this macro */
  PROC DATASETS LIBRARY=work NOLIST NOWARN;
    DELETE _mch_:;
  RUN; QUIT;
%MEND m_chg;

%m_baseline marks the baseline record of each group. Its defaults are the SAP rule of this study: by=usubjid paramcd, where=adt <= trtsdt AND NOT MISSING(aval), and sortby= the by= variables plus adt. The last record of a group that meets where= is the baseline. The WHERE= option works before BY, so LAST. sees only the records that can be baseline.
copy= chooses the data set structure. copy=N sets ABLFL = "Y" on the collected record itself, as the reference ADVS does. copy=Y keeps that record and adds a copy of it with AVISIT = "Baseline": many companies want the baseline as its own analysis visit. The ADaM implementation guide allows both designs. The SAP and the table shells (the empty mock tables that show each table’s layout) decide. Look at the data first. Here the collected BASELINE visit records already have AVISIT = "Baseline". So with copy=Y, 2,277 of the 2,286 blood pressure groups get two Baseline rows. Only the 9 groups of 01-718-1150 get one, because its baseline comes from SCREENING 1. Use copy=Y only when the collected pre-dose records have no analysis visit, as for the lab data in 10.4, or mark the copy as your specification says, for example with DTYPE.
%m_chg checks its precondition. Two baseline records in one group would give two values of BASE. Then a merge would repeat records, and SAS would not tell you. The macro counts them and stops with ERROR: [M_CHG] ... instead.
%m_chg computes CHG on every record with AVAL and BASE. On the baseline record CHG = 0, and records before it get a value too: the screening weight of 01-701-1015 is 53.98, BASE 54.43, so CHG = -0.45. That is a specification decision, not a law. The reference ADVS does the same (CHG = 0 on its 2,277 baseline records); the reference ADLBC leaves CHG empty on its baseline records. Exercise 10-6 writes the other rule.
Two new words in the first lines of each macro. / DES = '...' stores a short description with the compiled macro. &SYSMACRONAME is the name of the macro that runs now. So %LET macro = %UPCASE(&SYSMACRONAME.); gives every message the right name, even after you rename the macro.

Now the program. Steps 020 and 030 are one call each. Then a test: %m_chg on weight must give the same result as the hand-written steps of 10_1_advs.sas:

/* Chapter 10: blood pressure and pulse with the study macros %m_baseline and %m_chg (needs ADSL from 09_1, ADVS_020 from 10_1 and the macros of 10_2_0) */

/*****************************************************************************/
/* Example SAP definition: Baseline                                          */
/* Baseline is the last non-missing value on or before the day of the first  */
/* dose. Blood pressure and pulse are measured in three positions: each      */
/* position has its own baseline.                                            */
/*****************************************************************************/

/* Step 010: one record per measurement, with date, study day and position */
DATA advs_bp_010;
  LENGTH paramcd $8 param $40 atpt $40 avisit $20;
  MERGE sdtm.vs(IN=invs WHERE=(vstestcd IN ("SYSBP" "DIABP" "PULSE"))
                KEEP=usubjid vsseq vstestcd vstest vstpt vstptnum visitnum visit vsdtc vsstresn vsstresu)
        adsl(IN=inadsl KEEP=usubjid trtsdt);
  BY usubjid;
  IF invs AND inadsl;
  paramcd = vstestcd;
  param   = CATS(vstest, " (", vsstresu, ")");
  atpt    = vstpt;
  atptn   = vstptnum;
  aval    = vsstresn;
  adt     = INPUT(vsdtc, ?? E8601DA.);
  IF      adt >= trtsdt    THEN ady = adt - trtsdt + 1;
  ELSE IF NOT MISSING(adt) THEN ady = adt - trtsdt;
  IF visit = "BASELINE" THEN DO;
    avisit  = "Baseline";
    avisitn = 0;
  END;
  ELSE IF SCAN(visit, 1) = "WEEK" THEN DO;
    avisitn = INPUT(SCAN(visit, 2), ?? 8.);
    avisit  = CATX(" ", "Week", avisitn);
  END;
  FORMAT adt DATE9.;
  DROP vstestcd vstest vstpt vstptnum vsdtc vsstresn vsstresu;
RUN;

/* Step 020: the baseline record, as the SAP says, not as the visit is called: */
/* the last non-missing value on or before the first dose, one per position   */
%m_baseline(data=advs_bp_010, out=advs_bp_020, by=usubjid paramcd atptn,
            sortby=usubjid paramcd atptn adt vsseq)

/* Step 030: BASE, CHG and PCHG, one baseline per position */
%m_chg(data=advs_bp_020, out=advs_bp, by=usubjid paramcd atptn)

/* Test the tool against the hand-written steps of 10_1: weight */
%m_chg(data=advs_020, out=advs_wt_m, by=usubjid paramcd)
PROC SORT DATA=advs_wt_m; BY usubjid adt vsseq; RUN;
PROC COMPARE BASE=advs COMPARE=advs_wt_m CRITERION=1E-8 NOSUMMARY;
  ID usubjid adt vsseq;
  VAR base chg pchg;
RUN;
%PUT NOTE: weight, macro against 10_1: SYSINFO=&SYSINFO. (0 means equal);

/* Compare blood pressure and pulse with the reference ADVS */
/* VSSEQ is the key of the SDTM record: one ADVS record per VS record.        */
/* The reference also has derived rows, End of Treatment (AVISITN 99): copies  */
/* of the last value on treatment, with the same VSSEQ. Compare the rest.      */
PROC SORT DATA=refadm.advs(WHERE=(paramcd IN ("SYSBP" "DIABP" "PULSE") AND avisitn NE 99)) OUT=ref_bp;
  BY usubjid vsseq;
RUN;
PROC SORT DATA=advs_bp OUT=our_bp;
  BY usubjid vsseq;
RUN;
PROC COMPARE BASE=ref_bp COMPARE=our_bp CRITERION=1E-8 NOSUMMARY;
  ID usubjid vsseq;
  VAR aval ablfl base chg;
RUN;
%PUT NOTE: blood pressure and pulse against the reference: SYSINFO=&SYSINFO.;

/* how many records differ, for which subjects, and in which variables? */
PROC SQL;
  TITLE "Records that differ from the reference ADVS";
  SELECT o.usubjid,
         SUM(o.ablfl NE r.ablfl)                       AS ablfl_differs,
         SUM(NOT (o.base = r.base))                    AS base_differs,
         SUM(NOT (o.chg  = r.chg))                     AS chg_differs,
         COUNT(*)                                      AS records
    FROM our_bp AS o INNER JOIN ref_bp AS r
         ON o.usubjid = r.usubjid AND o.vsseq = r.vsseq
    GROUP BY o.usubjid
    HAVING SUM(o.ablfl NE r.ablfl) + SUM(NOT (o.base = r.base)) + SUM(NOT (o.chg = r.chg)) > 0;
  TITLE "Where the baseline of these subjects comes from";
  SELECT usubjid, visit, COUNT(*) AS baseline_records
    FROM advs_bp
    WHERE ablfl = "Y" AND visit NE "BASELINE"
    GROUP BY usubjid, visit;
  TITLE;
QUIT;

/* Step 040: the same macro on weight: the SAP rule against the visit name of 10_1 */
%m_baseline(data=advs_020, out=advs_wt_sap, sortby=usubjid paramcd adt vsseq)
PROC SQL;
  TITLE "Weight: baseline by the visit name (10_1) or by the SAP rule";
  SELECT n.usubjid, n.visit, n.adt, n.aval, n.ablfl AS by_visit_name, s.ablfl AS by_sap_rule
    FROM advs_020 AS n INNER JOIN advs_wt_sap AS s
         ON n.usubjid = s.usubjid AND n.vsseq = s.vsseq
    WHERE n.ablfl NE s.ablfl;
  TITLE;
QUIT;

Two details of the calls:

sortby= must make the order unique. Two records of a group can have the same date. vsseq, the sequence number of the SDTM record, decides which one is last, so the result is the same in every run.
The last call uses only defaults for the rule: weight has one value per visit, so by=usubjid paramcd is right.

What you should see:

NOTE: [M_BASELINE] Version 2.0 started 01NOV2026 22:33
NOTE: [M_CHG] Version 1.1 started 01NOV2026 22:33
NOTE: The data set WORK.ADVS_BP has 24619 observations and 18 variables.
NOTE: weight, macro against 10_1: SYSINFO=0 (0 means equal)
NOTE: blood pressure and pulse against the reference: SYSINFO=4096

SYSINFO=4096 means: the values of some variables differ; the records themselves match, 24,619 on both sides. The summary query shows where:

USUBJID	ablfl_differs	base_differs	chg_differs	records
01-718-1150	9	108	108	108

USUBJID	Visit Name	baseline_records
01-718-1150	SCREENING 1	9

One subject, and only one. Subject 01-718-1150 has no BASELINE visit for blood pressure. The SAP rule takes the last value before the first dose, from SCREENING 1, for all 9 parameter and position pairs. The reference takes the baseline from the visit called BASELINE, so this subject has no baseline and no change there.

Step 040 asks the same question for weight. 10_1_advs.sas took the baseline from the visit called BASELINE; the macro takes it by the SAP rule. One record differs:

USUBJID	Visit Name	adt	aval	by_visit_name	by_sap_rule
01-702-1082	SCREENING 1	03JUL2013	54.43		Y

Subject 01-702-1082 has no weight at the BASELINE visit. So the visit rule gives no baseline, and the SAP rule takes the screening weight. Your program follows the SAP. A difference from the reference is a question for the statistician, not an error. The case study From the SAP to a study macro shows how to design a study macro like %m_baseline: from the SAP text to the header, the checks and the tests.

Two more facts the comparison taught:

The reference has rows that SDTM does not have. It has 2,043 records with AVISIT = "End of Treatment" (AVISITN = 99). They are copies of the last value on treatment, with the same VSSEQ. Such a derived row is a record that the ADaM programmer adds. It has no SDTM record of its own. The comparison leaves them out (avisitn NE 99) and compares the 24,619 collected records.
A key must be unique. The first version compared BY usubjid paramcd atptn adt and SAS wrote WARNING: ... contains a duplicate observation. VSSEQ is unique per subject.

WORK.ADVS_BP with WHERE usubjid = “01-718-1150” AND ablfl = “Y”: 9 of 24619 records, one per parameter and position.

Exercise 10-2. Set ablfl = "Y" on every BASELINE visit record of weight in ADVS_020 and on the screening record of subject 01-701-1015, and call %m_chg on it. What does the log say, and what is in WORK._MCH_021?

Exercise 10-3. The reference ADVS has 2,043 End of Treatment records for blood pressure and pulse. Show with PROC SQL that each one has the same VSSEQ as another record of the same subject and parameter. From which analysis visits are they copied?

10.3 Analysis visits: as collected, or by study day?

The reference ADVS uses the visit as it was collected. A weight from the visit called WEEK 6 is Week 6, even when the subject came three weeks late. Many SAPs ask for analysis windows instead. Each visit has a target day, and every record goes to the visit whose window contains its study day. In ADaM, AWTARGET is the target day, and AWLO and AWHI are the first and the last day of the window.

A common rule puts the window limits halfway between two target days, and a tie goes to the later visit. The program reads WORK.ADVS and keeps the windows in a data set. Then one join gives each post-baseline weight its window: the window whose AWLO and AWHI contain its study day. AWTDIFF is the distance in days between the study day and the target day.

/* Chapter 10: analysis windows by study day, the other way to assign a visit (needs ADVS from 10_1) */

/* Step 010: the windows as data: one row per planned visit */
/* target day 7 * week + 1; the limits lie halfway between two targets; a tie goes to the later visit */
DATA windows;
  LENGTH avisit $20;
  ARRAY wk{9} _TEMPORARY_ (2 4 6 8 12 16 20 24 26);
  DO i = 1 TO 9;
    avisitn  = wk{i};
    avisit   = CATX(" ", "Week", wk{i});
    awtarget = 7 * wk{i} + 1;
    IF i < 9 THEN awhi = CEIL((awtarget + 7 * wk{i + 1} + 1) / 2) - 1;
    ELSE          awhi = .;
    IF i = 1 THEN awlo = 2;
    OUTPUT;
    IF i < 9 THEN awlo = awhi + 1;
  END;
  DROP i;
RUN;

PROC PRINT DATA=windows NOOBS; VAR avisit avisitn awlo awtarget awhi; RUN;

/* Step 020: the post-baseline weights, with the visit they were collected at */
DATA post;
  SET advs(KEEP=usubjid vsseq visit ady aval avisitn);
  WHERE avisitn > 0;
  nominal = avisitn;
  DROP avisitn;
RUN;

/* Step 030: each record goes to the window that contains its study day */
/* the last window has no upper limit (awhi missing) */
PROC SQL;
  CREATE TABLE advs_win AS
    SELECT p.*, w.avisit, w.avisitn, w.awlo, w.awtarget, w.awhi, p.ady - w.awtarget AS awtdiff
      FROM post AS p LEFT JOIN windows AS w
           ON p.ady >= w.awlo AND (p.ady <= w.awhi OR MISSING(w.awhi))
      ORDER BY p.usubjid, p.ady, p.vsseq;
QUIT;

/* Step 040: how many weights land in another visit than the one they were collected at? */
DATA moved;
  SET advs_win;
  LENGTH result $20;
  IF      MISSING(avisitn)   THEN result = "outside all windows";
  ELSE IF avisitn = nominal  THEN result = "same visit";
  ELSE                            result = "other visit";
RUN;

PROC FREQ DATA=moved; TABLES result / NOCUM; RUN;

PROC PRINT DATA=advs_win(WHERE=(usubjid = "01-705-1292")) NOOBS;
  VAR usubjid visit ady nominal avisit awtarget awtdiff;
RUN;

What you should see: the windows

AVISIT	AWLO	AWTARGET	AWHI
Week 2	2	15	21
Week 4	22	29	35
Week 6	36	43	49
Week 8	50	57	70
Week 12	71	85	98
Week 16	99	113	126
Week 20	127	141	154
Week 24	155	169	175
Week 26	176	183	(open)

and the result:

result	Frequency	Percent
other visit	85	5.51
same visit	1457	94.49

85 of the 1542 post-baseline weights, 5.5%, land in another visit when you use windows. Subject 01-705-1292 shows why:

VISIT	ADY	collected as	window says
WEEK 2	16	2	Week 2
WEEK 4	40	4	Week 6
WEEK 6	59	6	Week 8
WEEK 8	81	8	Week 12
WEEK 12	117	12	Week 16
WEEK 16	141	16	Week 20
WEEK 20	169	20	Week 24
WEEK 24	197	24	Week 26
WEEK 26	213	26	Week 26

This subject came later and later. By the window rule, the visit called WEEK 4 measured the weight of week 6.

Neither rule is wrong: the SAP decides, and the decision changes 85 values in this small study. Write the rule down before you program it, and keep the windows in a data set, as here, so that a reviewer can read them.

The windows are data, not code. A new visit is one more row in WINDOWS, and the join does not change. That is the same idea as the study macro: keep the rule where a reader finds it, and keep the mechanism small.

Exercise 10-4. In 10_3_advs_windows.sas, move the ties to the earlier visit. How many records change their window?

10.4 Laboratory data (ADLB)

ADLB has the same structure: each lab test is a parameter (ALT, AST, BILI, …), and the same derivations apply. So the two macros of 10.2 should work without a new line of code. Test it on alanine aminotransferase (ALT), and compare with the reference ADLBC:

/* Chapter 10: the same two macros on a laboratory parameter, ALT (needs ADSL from 09_1 and the macros of 10_2_0) */

/* Step 010: one record per ALT result, with date and first dose date */
DATA adlb_010;
  LENGTH paramcd $8;
  MERGE sdtm.lb(IN=inlb WHERE=(lbtestcd = "ALT")
                KEEP=usubjid lbseq lbtestcd visitnum visit lbdtc lbstresn)
        adsl(IN=inadsl KEEP=usubjid trtsdt);
  BY usubjid;
  IF inlb AND inadsl;
  paramcd = lbtestcd;
  aval    = lbstresn;
  adt     = INPUT(lbdtc, ?? E8601DA.);
  FORMAT adt DATE9.;
  DROP lbtestcd lbdtc lbstresn;
RUN;

/* Step 020: the baseline as the SAP says, then BASE and CHG: two calls, no new code */
%m_baseline(data=adlb_010, out=adlb_020, sortby=usubjid paramcd adt lbseq)
%m_chg(data=adlb_020, out=adlb_alt, by=usubjid paramcd)

/* Step 030: compare with the reference ADLBC; leave out its derived End of Treatment rows */
PROC SORT DATA=refadm.adlbc(WHERE=(paramcd = "ALT" AND avisit NE "End of Treatment")) OUT=ref_alt;
  BY usubjid lbseq;
RUN;
PROC SORT DATA=adlb_alt OUT=our_alt;
  BY usubjid lbseq;
RUN;
PROC COMPARE BASE=ref_alt COMPARE=our_alt CRITERION=1E-8 NOSUMMARY;
  ID usubjid lbseq;
  VAR aval ablfl base chg;
RUN;
%PUT NOTE: ALT against the reference: SYSINFO=&SYSINFO.;

/* where does the baseline come from, in our data and in the reference? */
PROC SQL;
  TITLE "Visit of the baseline record";
  SELECT "ours" AS source, visit, COUNT(*) AS records FROM our_alt WHERE ablfl = "Y" GROUP BY visit
  UNION ALL
  SELECT "reference" AS source, visit, COUNT(*) AS records FROM ref_alt WHERE ablfl = "Y" GROUP BY visit;
  TITLE;
QUIT;

What you should see: WORK.ADLB_ALT has 1814 observations, and

NOTE: ALT against the reference: SYSINFO=4224

4224 is 4096 + 128: some values differ, and our data set has records that the reference does not have. The 2 extra records come from the visits AMBUL ECG REMOVAL and RETRIEVAL, which the reference leaves out of ADLBC. The values differ for two reasons. The first is a convention: on the baseline record, the reference leaves CHG empty, and %m_chg gives CHG = 0 (252 records). The ADaM standard allows both; the specification decides. The second reason is the baseline rule. The last query shows where the baseline comes from:

source	Visit Name	records
ours	SCREENING 1	230
ours	UNSCHEDULED 1.1	18
ours	UNSCHEDULED 1.2	4
ours	UNSCHEDULED 1.3	2
reference	SCREENING 1	252

The chemistry labs have no BASELINE visit in the pilot. Some subjects had a lab test again after screening and before the first dose, at an unscheduled visit. The SAP rule takes the last value before the first dose. So for 24 subjects the baseline is the unscheduled value:

Subjects	Screening ALT	Unscheduled retest	Result against the reference
20	yes	yes, another value	another `BASE`
2 (01-703-1197, 01-704-1008)	yes	yes, the same value	the same `BASE`, from another record
2 (01-703-1119, 01-708-1348)	no	yes	the reference has no baseline (252 baseline records against our 254)

So BASE changes for 22 subjects. An example is subject 01-701-1239: screening ALT 64 on 28DEC2013, retest 61 on 06JAN2014. The reference says BASE = 64. The SAP rule says BASE = 61.

The macros did not change. A new BDS data set is two calls, and the comparison finds the places where the rule matters.

Exercise 10-5. Call %m_baseline on adlb_010 with copy=Y, then %m_chg on the result. How many records does the output have, and how many have AVISIT = "Baseline"? Is BASE on the collected records the same as in ADLB_ALT?

Exercise 10-6. Your specification says: CHG and PCHG only after baseline, empty on the baseline record and before it. Write that rule in a DATA step after the %m_chg call of 10_4_adlb.sas, without changing the macro. How many CHG values become empty? Does SYSINFO of the ALT comparison change, and why?

Two more things belong to ADLB. The first is the normal range: ANRLO, ANRHI, and ANRIND (LOW, NORMAL or HIGH). The second is the shift table, from the baseline ANRIND to the worst post-baseline ANRIND. Both are a few lines in a DATA step after %m_baseline.

Exercises

The exercises are next to the program they change: 10-1 changes 10_1_advs.sas, 10-2 and 10-3 use 10_2_advs_bp.sas, 10-4 changes 10_3_advs_windows.sas, 10-5 and 10-6 use 10_4_adlb.sas. The answers are in Answers, Part II.