Effective Use of the RETAIN Statement in Programming Clinical Trial

1. Effective Use of the RETAIN Statement in Programming Clinical Trial Mingxia Chen Biostatistician Beijing, China

2. Introduction • RETAIN statement causes a variable that is created by an INPUT or assignment statement to retain its value from one iteration of the DATA step to the next ---- SAS help document • In contrast to the default DATA step behavior, without RETAIN statement, SAS automatically sets new variables in the data step to missing at the start of each iteration • The RETAIN statement is very useful to perform data manipulation across observations. • RETAIN is simpler and more flexible, to speed up your program.

3. Example 1 Concatenate character values from multiple records of the same variable

4. Original Dataset (SUPPEX) Result Dataset (REASON)

5. Original Dataset (SUPPEX) 100-5001 EXSEQ 2 IIREAS Reason for injection interruption Adverse Event 100-5001 EXSEQ 2 IISPEC Specify for interruption ALLERGIC REACTION, SKIN RASH, DYSPNEA Result Dataset (RSLTDS)

6. Prevents the 2 variables from being reset to missing for each iteration. Assigns initial value (missing) to EXLOT, REASON Concatenates character values of QVAL in multiple records Keeps the last observation per SUBJECT per EXSEQ SAS Code PROCSORTDATA=SUPPEX; BY USUBJID IDVAR IDVARVAL QNAM; RUN; DATA RSLTDS(KEEP=USUBJID EXSEQ EXLOT REASON); SET SUPPEX; BY USUBJID IDVAR IDVARVAL QNAM; FORMAT EXLOT REASON $200.; RETAIN EXLOT REASON; IF FIRST.IDVARVAL THEN DO; EXLOT=""; REASON =""; END; IF INDEX(QNAM,“EXLOT")>0 THEN EXLOT=CATX(“, ", OF EXLOT QVAL); IF QNAM IN (“IIREAS", “IISPEC") THEN REASON=CATX(": ", OF REASON QVAL); IF LAST.IDVARVAL; EXSEQ=INPUT(IDVARVAL, best.); RUN;

7. Example 2 Select the previous minimum sum of longest diameters in oncology clinical trials

8. Original dataset (TR)

9. Result Dataset (PREMINSL)

10. Prevents PREMINSL from being reset to missing for each iteration LAG function to Get the value of the last observation Sets LASTSL to missing for the first visit of each SUBJECT Sets PREMINSL to missing for the first visit of each SUBJECT Compares PREMINSL to LASTSL in current iteration and resets value if LASTSL is smaller SAS Code PROCSORTDATA=TR; BY USUBJID TRTESTCD VISITNUM; RUN; DATA PREMINSL(DROP=LASTSL); SET TR; BY USUBJID TRTESTCD VISITNUM; RETAIN PREMINSL ; LASTSL=LAG(TRSTRESN); IF FIRST.TRTESTCD THEN DO; LASTSL=.; PREMINSL=.; END; ELSE PREMINSL=MIN(PREMINSL, LASTSL); RUN;

11. Example 3 Count the number of observations (example AE table summary)

12. AE Summary Table

13. Analysis Dataset (ADAE)

14. SQL Procedure

15. Result Dataset (AECNT)

16. Assigns the initial value (0) for the 6 variables Assigns the initial value (0) of each AE category for each subject Assigns the 0 for each treatment group Count Number of subject (SCNT{i}) and Number of AEs (AECNT{i} per AE category. Count the number of AEs per SUBJECT for each AE category. SAS Code PROCSORTDATA=ADAE; BY TRTAN USUBJID AESEQ; RUN; DATA AECNT(KEEP=TRTAN COL1 COLU1 SUBJCNT EVECNT); SET ADAE; BY TRTAN USUBJID AESEQ; ARRAY AEFL{3} $ TEAEFL RTEAEFL TESAEFL; ARRAY SUBJAE{3} SUBJAE1-SUBJAE3; ARRAY SCNT{3} SCNT1-SCNT3; ARRAY AECNT {3} AECNT1-AECNT3; RETAIN SCNT1-SCNT3 AECNT1-AECNT3 0; IF FIRST.TRTAN THEN DO I=1TOhbound(SCNT); SCNT{I}=0; AECNT{I}=0; END; IF FIRST.USUBJID THEN DO I=1TOhbound(SUBJAE); SUBJAE{I}=0; END; DO I=1 TO hbound(SUBJAE); IF AEFL{I}='Y' THEN SUBJAE{I}+1; END; IF LAST.USUBJID THEN DO I=1TOhbound(SCNT); IF SUBJAE{I}>=1THEN SCNT{I}=SCNT{I}+1; AECNT{I}=AECNT{I}+SUBJAE{I}; END; IF LAST.TRTAN THEN DO I=1 TOhbound(SCNT); COL1=I; COLU1=PUT(COL1, COL1F.); SUBJCNT=SCNT{I}; EVECNT=AECNT{I}; OUTPUT; END; RUN; Output the Number of subject and Number or events for each category.

17. Conclusion The RETAIN statement can carry over values from one observation to next, so it is very useful to manipulate the data across observations.

18. Q&AThanks