Answers, Parts IV and V

Chapter 15: Validation and QC

15-1. Replace the SQL with a sort of EX by usubjid and exstdtc, a DATA step with BY usubjid, RETAIN for the two dates, and trtdurd = trtedt - trtsdt + 1 on the last record. The compare must still report No unequal values were found. If it does not, one of the two programs has a different rule, and that is exactly what double programming exists to find.

15-2. Two numbers computed in different ways (for example a mean computed by PROC MEANS and by PROC SQL) can differ in the last binary digit. CRITERION=1E-8 tells PROC COMPARE to treat differences smaller than that as equal. Use it only for computed decimals, never for dates, counts or codes.

15-3. Join the 6 subjects from the compare (OUTNOEQUAL with OUT=) with sdtm.ex by usubjid and print all their EX records, sorted by exstdtc. Look at which record has no EXENDTC.

15-4. The rule is already in the macro: ELSE IF INDEX(line, "Division by zero") THEN kind = "NOTE: division by zero";. Test it with a step that computes x = 1 / 0; between the two PROC PRINTTO steps.

Chapter 16: How to solve a programming problem

16-1. SELECT COUNT(*) FROM sdtm.ex WHERE LENGTH(exstdtc) < 10;

16-2. Only step 020 changes: the ELSE DO branch sets trtedtc = "" and a source such as "MISSING". Steps 010 and 030 stay the same. That is the point of keeping decisions apart from computation.

16-3. Your note should say: the earliest EX start date per subject; source EX; open question: what about partial start dates?

Chapter 17: How the macro processor works

17-1. PROC SQL NOPRINT; SELECT DISTINCT trt01p INTO :trt1- FROM refadm.adsl; QUIT; then %DO i = 1 %TO &sqlobs.; %PUT &&trt&i.; %END; inside a macro.

17-2. %EVAL(10 / 4) gives 2: whole-number division drops the remainder. %SYSEVALF(10 / 4, CEIL) gives 3: 2.5 rounded up.

17-3. %LET x = %NRSTR(Smith & Jones);

17-4. Use &when. in a later step, for example %PUT &=when.; after RUN;, or read it inside the same step with SYMGET("when"), which looks it up while the step runs.

Chapter 18: Code from data

18-1. Add exclude= and change the query to SELECT DISTINCT paramcd, param ... WHERE paramcd NOT IN (%SYSFUNC(TRANWRD(%SYSFUNC(COMPBL("&exclude.")), %STR( ), %STR(" ")))). Quote each code in the list.

18-2. Read a fourth part with %SCAN(&item., 4, |). When it is not empty, write label = PUT("&lab.", &fmt.);, else keep the text.

18-3. With DOSUBL, each row runs its query and gives the count back in the same step: no %NRSTR and no timing trap. For a few rows it is easier to read. For many rows CALL EXECUTE is faster, because DOSUBL starts a side session each time.

18-4. PROC CONTENTS ... OUT= also gives FORMAT, FORMATL (the width) and FORMATD (the decimals). Build the text only for variables that have a format:

PROC CONTENTS DATA=refadm.adsl OUT=s4(KEEP=name format formatl formatd) NOPRINT; RUN;
PROC SQL NOPRINT;
  SELECT CATX(" ", name, CATS("FORMAT=", format, IFC(formatl > 0, CATS(formatl), ""), ".",
                              IFC(formatd > 0, CATS(formatd), "")))
    INTO :fmts SEPARATED BY " " FROM s4 WHERE NOT MISSING(format);
QUIT;

What you should see: 5 variables with a format, all dates: DISONSDT FORMAT=DATE9. RFENDT FORMAT=DATE9. TRTEDT FORMAT=DATE9. TRTSDT FORMAT=DATE9. VISIT1DT FORMAT=DATE9. Add &fmts. to the ATTRIB statement of step 040.

Chapter 19: Build, test and debug a macro library

19-1. Loop over the BY variables with %SCAN and %SYSFUNC(VARNUM(...)) after %SYSFUNC(OPEN(&data.)), and stop with ERROR: [COUNT_SUBJ] Variable ... not in .... Test E03 then shows one error and passes.

19-2. Write T03 first: it fails, because pct= does not exist. Then add a step that joins the counts with the big N from ADSL and computes pct = n_subj / bign * 100. T03 passes.

19-3. Write the macro text into mymacros/count_subj.sas with the same DATA _NULL_; FILE ...; PUT ...; technique, or copy the file there, then call %count_subj(...) with no %INCLUDE.

Case study: from the SAP to a study macro

1. Average first, then take the last. Before the %m_baseline call, a PROC MEANS NWAY with CLASS usubjid paramcd atptn visitnum adt computes the mean of the repeated measurements of each time point; its output is the data= of %m_baseline, with by=usubjid paramcd atptn as for blood pressure. Mark the new records as averages: the ADaM variable DTYPE is "AVERAGE" for a record whose value is derived from several records. The pilot data has no repeated measurements, so test the program with a small data set you make yourself.
2. Add debug=N to the parameter list, and run the cleanup only when %UPCASE(&debug.) NE Y, as in %count_subj of chapter 19.
3. %m_baseline(data=advs_bp_010, out=ex3_copy, by=usubjid paramcd atptn, sortby=usubjid paramcd atptn adt vsseq, copy=Y) adds 2286 records, one per subject, parameter and position with a pre-dose value: 26905 records in all (24619 + 2286). %m_chg(data=ex3_copy, out=ex3_chg, by=usubjid paramcd atptn) then gives every collected record the same BASE as in ADVS_BP: 0 differences. Flag or copy, the baseline value is the same.

Case study: list processing with %m_loop

1. Five. Add durdsgr1 /* Disease duration: <12, >=12 months */ to the list; the %m_loop call does not change. The log says NOTE: tables made by %m_loop: 5 (count it with the same %PUT).

2. %m_loop calls the macro once with an empty each, so by is empty and PROC SQL fails on SELECT b., .... The counter still says 1, because the macro counts the table even when the step fails. A fix at the start of %chg_table: %IF %LENGTH(&by.) = 0 %THEN %DO; %LET by = allsubj; %LET by_title = all subjects; %END; and in the SQL SELECT %IF &by. = allsubj %THEN "All" AS allsubj; %ELSE b.&by.; , a.trtpn, .... The table then shows all subjects: Placebo 58, mean -2.12; Xanomeline Low Dose 27, mean -0.26; Xanomeline High Dose 30, mean -5.60.

3. &each. in the macro= value is resolved when %m_loop is called, before each exists: WARNING: Apparent symbolic reference EACH not resolved. (the tables are still made, because the text is resolved again in the loop). Do not hide it with %NRSTR(&each.): the quoting stays on the value, and the WHERE clause gets the text &each., a syntax error. Write a small macro whose default reads each, the same trick as by=&each.:

   %MACRO sbp_position(atptn=&each.);
     %chg_table(paramcd=SYSBP, atptn=&atptn., by=sex, by_title=Sex)
   %MEND sbp_position;
   %m_loop(macro=sbp_position, list=815|816|817, delimiter=|)

   Three tables, no WARNING.