TABLE 14.2.1: GENERATING A DETAILED SUMMARY OF ADVERSE EVENTS BY SYSTEM ORGAN CLASS AND PREFERRED TERM – SAFETY POPULATION USING PROC SQL | PROC SORT | PROC TRANSPOSE | PROC REPORT IN BASE SAS WITH MACRO AUTOMATION AND MOCK SHELL DESIGN FOR CLINICAL TRIAL REPORTING
/*Creating a Mock Shell and Table in SAS is an essential part of the clinical programming process*/
1. What is a Mock Shell?
A mock shell is a static visual representation of how the final table or listing should appear. It is usually made in Microsoft Word or Excel and is based on the SAP. It shows the layout including:
1.Table title and number
2.Column headings
3.Row groupings (e.g., treatment groups, visits)
4.Sub-totals and totals
5.Footnotes, population definitions
6.Page layout
Mock shells are used as templates for programmers to write the actual SAS code.
2. Why Are Mock Shells Important?
Standardization: Ensures uniformity across multiple tables/listings.
Approval: Statisticians and stakeholders review and approve the layout before programming.
Reference: Acts as a reference during programming and QC.
Traceability: Links back to SAP and Protocol objectives.
3. Sample Mock Shell – Table Layout
Let’s create a mock shell for a Table 14.2.1: Summary of Adverse Events by System
Organ Class and Preferred Term for a fictional clinical trial.
Table 14.2.1
Summary of Adverse Events by System Organ Class and Preferred Term – Safety Population
| System Organ Class / Preferred Term | Placebo (N=50) | Drug A (N=55) | Total
(N=105) |
|-------------------------------------|----------------|---------------|---------------|
| Infections and infestations | 10 (20.0%) | 15 (27.3%) | 25 (23.8%) |
|
Nasopharyngitis |
5 (10.0%) | 6 (10.9%) | 11 (10.5%) |
| Urinary tract
infection | 2
(4.0%) | 5 (9.1%) | 7 (6.7%) |
| Nervous system disorders | 8 (16.0%) | 12 (21.8%) | 20 (19.0%) |
| Headache | 4 (8.0%) | 6 (10.9%) | 10 (9.5%) |
Notes:
- Includes only Treatment Emergent AEs.
- Percentages are based on number of subjects in the
population.
4. Sample Data Preparation in SAS
Let’s simulate the Adverse Events dataset (ADAE) with a simplified structure.
Step I: Creating ADAE Dataset
data adae;
length USUBJID $10 TRT01A $10 AESOC $50 AEDECOD $50;
input USUBJID $ TRT01A $ AESOC $ AEDECOD $;
datalines;
101 Placebo Infections_and_infestations Nasopharyngitis
102 Placebo Infections_and_infestations Nasopharyngitis
103 Placebo Infections_and_infestations Urinary_tract_infection
104 Placebo Nervous_system_disorders Headache
105 Placebo Nervous_system_disorders Headache
106 Placebo Nervous_system_disorders Headache
107 Placebo Infections_and_infestations Nasopharyngitis
108 Placebo Nervous_system_disorders Headache
109 Placebo Nervous_system_disorders Headache
110 Placebo Nervous_system_disorders Headache
201 DrugA Infections_and_infestations Nasopharyngitis
202 DrugA Infections_and_infestations Nasopharyngitis
203 DrugA Infections_and_infestations Nasopharyngitis
204 DrugA Infections_and_infestations Urinary_tract_infection
205 DrugA Infections_and_infestations Urinary_tract_infection
206 DrugA Nervous_system_disorders Headache
207 DrugA Nervous_system_disorders Headache
208 DrugA Nervous_system_disorders Headache
209 DrugA Nervous_system_disorders Headache
210 DrugA Nervous_system_disorders Headache
211 DrugA Nervous_system_disorders Headache
212 DrugA Infections_and_infestations Nasopharyngitis
213 DrugA Infections_and_infestations Nasopharyngitis
;
run;
proc print;run;
Output:
| Obs | USUBJID | TRT01A | AESOC | AEDECOD |
|---|---|---|---|---|
| 1 | 101 | Placebo | Infections_and_infestations | Nasopharyngitis |
| 2 | 102 | Placebo | Infections_and_infestations | Nasopharyngitis |
| 3 | 103 | Placebo | Infections_and_infestations | Urinary_tract_infection |
| 4 | 104 | Placebo | Nervous_system_disorders | Headache |
| 5 | 105 | Placebo | Nervous_system_disorders | Headache |
| 6 | 106 | Placebo | Nervous_system_disorders | Headache |
| 7 | 107 | Placebo | Infections_and_infestations | Nasopharyngitis |
| 8 | 108 | Placebo | Nervous_system_disorders | Headache |
| 9 | 109 | Placebo | Nervous_system_disorders | Headache |
| 10 | 110 | Placebo | Nervous_system_disorders | Headache |
| 11 | 201 | DrugA | Infections_and_infestations | Nasopharyngitis |
| 12 | 202 | DrugA | Infections_and_infestations | Nasopharyngitis |
| 13 | 203 | DrugA | Infections_and_infestations | Nasopharyngitis |
| 14 | 204 | DrugA | Infections_and_infestations | Urinary_tract_infection |
| 15 | 205 | DrugA | Infections_and_infestations | Urinary_tract_infection |
| 16 | 206 | DrugA | Nervous_system_disorders | Headache |
| 17 | 207 | DrugA | Nervous_system_disorders | Headache |
| 18 | 208 | DrugA | Nervous_system_disorders | Headache |
| 19 | 209 | DrugA | Nervous_system_disorders | Headache |
| 20 | 210 | DrugA | Nervous_system_disorders | Headache |
| 21 | 211 | DrugA | Nervous_system_disorders | Headache |
| 22 | 212 | DrugA | Infections_and_infestations | Nasopharyngitis |
| 23 | 213 | DrugA | Infections_and_infestations | Nasopharyngitis |
5. Step-by-Step Table Programming in SAS
We will follow these steps to generate the mock shell table:
Step A: Derive Treatment Population Counts
proc sql;
create table pop_counts as
select TRT01A, count(distinct USUBJID) as N
from adae
group by TRT01A;
quit;
proc print;run;
Output:
| Obs | TRT01A | N |
|---|---|---|
| 1 | DrugA | 13 |
| 2 | Placebo | 10 |
Step B: Derive AE Counts by SOC/PT and Treatment
proc sql;
create table ae_counts as
select AESOC, AEDECOD, TRT01A, count(distinct USUBJID) as count
from adae
group by AESOC, AEDECOD, TRT01A;
quit;
proc print;run;
Output:
| Obs | AESOC | AEDECOD | TRT01A | count |
|---|---|---|---|---|
| 1 | Infections_and_infestations | Nasopharyngitis | DrugA | 5 |
| 2 | Infections_and_infestations | Nasopharyngitis | Placebo | 3 |
| 3 | Infections_and_infestations | Urinary_tract_infection | DrugA | 2 |
| 4 | Infections_and_infestations | Urinary_tract_infection | Placebo | 1 |
| 5 | Nervous_system_disorders | Headache | DrugA | 6 |
| 6 | Nervous_system_disorders | Headache | Placebo | 6 |
Step C: Create Total Counts
proc sql;
create table total_counts as
select AESOC, AEDECOD, count(distinct USUBJID) as count
from adae
group by AESOC, AEDECOD;
quit;
proc print;run;
Output:
| Obs | AESOC | AEDECOD | count |
|---|---|---|---|
| 1 | Infections_and_infestations | Nasopharyngitis | 8 |
| 2 | Infections_and_infestations | Urinary_tract_infection | 3 |
| 3 | Nervous_system_disorders | Headache | 12 |
Step D: Merge with Population Counts to Get Percentages
proc sql;
create table ae_percent as
select a.AESOC, a.AEDECOD, a.TRT01A, a.count,
b.N,
put(a.count, 3.) || ' (' || put((a.count/b.N)*100, 5.1) || '%)' as result
from ae_counts a
left join pop_counts b
on a.TRT01A = b.TRT01A;
quit;
proc print;run;
Output:
| Obs | AESOC | AEDECOD | TRT01A | count | N | result |
|---|---|---|---|---|---|---|
| 1 | Nervous_system_disorders | Headache | DrugA | 6 | 13 | 6 ( 46.2%) |
| 2 | Infections_and_infestations | Urinary_tract_infection | DrugA | 2 | 13 | 2 ( 15.4%) |
| 3 | Infections_and_infestations | Nasopharyngitis | DrugA | 5 | 13 | 5 ( 38.5%) |
| 4 | Nervous_system_disorders | Headache | Placebo | 6 | 10 | 6 ( 60.0%) |
| 5 | Infections_and_infestations | Nasopharyngitis | Placebo | 3 | 10 | 3 ( 30.0%) |
| 6 | Infections_and_infestations | Urinary_tract_infection | Placebo | 1 | 10 | 1 ( 10.0%) |
Step E: Transpose Data for Final Layout
We want columns like Placebo, Drug A, and Total in the same row.
proc sort data=ae_percent; by AESOC AEDECOD TRT01A; run;
proc transpose data=ae_percent out=ae_final(drop=_NAME_) prefix=col_;
by AESOC AEDECOD;
id TRT01A;
var result;
run;
proc print;run;
Output:
| Obs | AESOC | AEDECOD | col_DrugA | col_Placebo |
|---|---|---|---|---|
| 1 | Infections_and_infestations | Nasopharyngitis | 5 ( 38.5%) | 3 ( 30.0%) |
| 2 | Infections_and_infestations | Urinary_tract_infection | 2 ( 15.4%) | 1 ( 10.0%) |
| 3 | Nervous_system_disorders | Headache | 6 ( 46.2%) | 6 ( 60.0%) |
Add Total column:
proc sql;
create table total_merge as
select a.*,
b.count as total_count,
put(b.count, 3.) || ' (' || put((b.count/105)*100, 5.1) || '%)' as col_Total
from ae_final a
left join total_counts b
on a.AESOC = b.AESOC and a.AEDECOD = b.AEDECOD;
quit;
proc print;run;
Output:
| Obs | AESOC | AEDECOD | col_DrugA | col_Placebo | total_count | col_Total |
|---|---|---|---|---|---|---|
| 1 | Infections_and_infestations | Nasopharyngitis | 5 ( 38.5%) | 3 ( 30.0%) | 8 | 8 ( 7.6%) |
| 2 | Infections_and_infestations | Urinary_tract_infection | 2 ( 15.4%) | 1 ( 10.0%) | 3 | 3 ( 2.9%) |
| 3 | Nervous_system_disorders | Headache | 6 ( 46.2%) | 6 ( 60.0%) | 12 | 12 ( 11.4%) |
Step F: Display Table Using PROC REPORT
title1 "Table 14.2.1";
title2 "Summary of Adverse Events by System Organ Class and Preferred Term – Safety Population";
proc report data=total_merge nowd headline headskip;
columns AESOC AEDECOD col_Placebo col_DrugA col_Total;
define AESOC / group "System Organ Class";
define AEDECOD / group "Preferred Term";
define col_Placebo / display "Placebo (N=50)";
define col_DrugA / display "Drug A (N=55)";
define col_Total / display "Total (N=105)";
run;
footnote1 "Includes only Treatment Emergent AEs.";
footnote2 "Percentages are based on number of subjects in the population.";
Output:
| Table 14.2.1 |
| Summary of Adverse Events by System Organ Class and
Preferred Term – Safety Population |
| System Organ Class | Preferred Term | Placebo (N=50) | Drug A (N=55) | Total (N=105) |
|---|---|---|---|---|
| Infections_and_infestations | Nasopharyngitis | 3 ( 30.0%) | 5 ( 38.5%) | 8 ( 7.6%) |
| Urinary_tract_infection | 1 ( 10.0%) | 2 ( 15.4%) | 3 ( 2.9%) | |
| Nervous_system_disorders | Headache | 6 ( 60.0%) | 6 ( 46.2%) | 12 ( 11.4%) |
Includes only Treatment Emergent AEs. |
| Percentages are based on number of subjects in the population. |
6. Enhancing with Macros
Let’s make this modular using a macro for reuse.
%macro summary_table(dataset=, soc=, pt=, trt=, tabletitle=);
proc sql;
create table pop_counts as
select &trt, count(distinct USUBJID) as N
from &dataset
group by &trt;
create table ae_counts as
select &soc, &pt, &trt, count(distinct USUBJID) as count
from &dataset
group by &soc, &pt, &trt;
create table total_counts as
select &soc, &pt, count(distinct USUBJID) as count
from &dataset
group by &soc, &pt;
create table ae_percent as
select a.&soc, a.&pt, a.&trt, a.count,
b.N,
put(a.count, 3.) || ' (' || put((a.count/b.N)*100, 5.1) || '%)' as result
from ae_counts a
left join pop_counts b
on a.&trt = b.&trt;
proc sort data=ae_percent; by &soc &pt &trt; run;
proc transpose data=ae_percent out=ae_final(drop=_NAME_) prefix=col_;
by &soc &pt;
id &trt;
var result;
run;
proc sql;
create table total_merge as
select a.*,
b.count as total_count,
put(b.count, 3.) || ' (' || put((b.count/105)*100, 5.1) || '%)' as col_Total
from ae_final a
left join total_counts b
on a.&soc = b.&soc and a.&pt = b.&pt;
quit;
title1 "&tabletitle";
proc report data=total_merge nowd headline headskip;
columns &soc &pt col_Placebo col_DrugA col_Total;
define &soc / group "System Organ Class";
define &pt / group "Preferred Term";
define col_Placebo / display "Placebo (N=50)";
define col_DrugA / display "Drug A (N=55)";
define col_Total / display "Total (N=105)";
run;
footnote1 "Includes only Treatment Emergent AEs.";
footnote2 "Percentages are based on number of subjects in the population.";
%mend;
%summary_table(
dataset=adae,
soc=AESOC,
pt=AEDECOD,
trt=TRT01A,
tabletitle=Table 14.2.1 - Summary of Adverse Events by SOC and PT);
Output:
| Table 14.2.1 - Summary of Adverse Events by SOC and
PT |
| System Organ Class | Preferred Term | Placebo (N=50) | Drug A (N=55) | Total (N=105) |
|---|---|---|---|---|
| Infections_and_infestations | Nasopharyngitis | 3 ( 30.0%) | 5 ( 38.5%) | 8 ( 7.6%) |
| Urinary_tract_infection | 1 ( 10.0%) | 2 ( 15.4%) | 3 ( 2.9%) | |
| Nervous_system_disorders | Headache | 6 ( 60.0%) | 6 ( 46.2%) | 12 ( 11.4%) |
Includes only Treatment Emergent AEs. |
| Percentages are based on number of subjects in the population. |
No comments:
Post a Comment