Adverse Event Tables

Safety reporting is one of the most difficult things for me as a clinical programmer.  I think it is mostly because it seems to be the boring part of clinical data analysis, but also because there is so much data, and it becomes a bit difficult for me to visualize.  But, like it or not, it is still in my job description to produce adverse event (AE) tables for safety reporting.


I have created a macro that is intended to standardize all my safety reporting.  I do not claim that this macro that I am going to present here is the best way or even that it is flawless, but it has helped me in summarizing my safety data.  I have not tried every possible way of passing data, so there may be some errors that need fixed.  If there are better macros out there, I would love to be referred to them.


This macro does not create the final output.  It only creates a table that can be output later.  The actual output requires a little bit of further programming, which I have discussed in a previous post.  I hope to continue to work on this macro to make it more general for final output.


Neverthesless, here is a description of all the parameters involved with this AESUM macro.

Macro Variable	Required?	Default Value	Description
AEDSN	Yes		Raw AE dataset with at least SUBJID, GRDVAR, SOC, and PREF
WHR	No	1=1 (Assigns the Boolean value of TRUE and captures all observations)	Condition for a WHERE statement if further filtering is needed on AEDSN
SAFDSN	Yes		Dataset containing a unique observation for each patient in the safety database
BYVAR	No	If not specified, this variable will be assigned a single distinct value.	Stratification variable of interest in AEDSN
GRDVAR	Yes		NCI Grade variable in AEDSN
SOC	Yes		System Organ Class variable in AEDSN
PREF	Yes		Preferred Term variable in AEDSN
SUBJID	Yes		Subject ID variable in AEDSN and SAFDSN
MINPERCENT	No	-1 (Captures all results)	A numeric flag denoting the minimum overall percent that should be reported
OUTDSN	Yes		User defined output dataset name
DISPLAYSOC	No	Y	Should the "N (XX.X%)" results be displayed for each SOC? - Y or N

EXAMPLE

This is just one example.  Since the code for these posts get to be a bit long and crowded, I am going to post a second post to this topic showing some additional ways to use this macro.


DATA

I am taking this data from Jack Shostack's book SAS Programming in the Pharmaceutical Industry.  The data can be found here.  (Scroll down to Program 5.4.)

/***  INPUT SAMPLE TREATMENT DATA  ***/

data treat;

label subjid = "Subject Number"

      trtcd  = "Treatment";     

input subjid trtcd @@;

datalines;

101 1  102 0  103 0  104 1  105 0  106 0  107 1  108 1  109 0  110 1

111 0  112 0  113 0  114 1  115 0  116 1  117 0  118 1  119 1  120 1

121 1  122 0  123 1  124 0  125 1  126 1  127 0  128 1  129 1  130 1

131 1  132 0  133 1  134 0  135 1  136 1  137 0  138 1  139 1  140 1

141 1  142 0  143 1  144 0  145 1  146 1  147 0  148 1  149 1  150 1

151 1  152 0  153 1  154 0  155 1  156 1  157 0  158 1  159 1  160 1

161 1  162 0  163 1  164 0  165 1  166 1  167 0  168 1  169 1  170 1

;

run;

/***  INPUT SAMPLE ADVERSE EVENT DATA  ***/

data ae;

label subjid   = "Subject Number"

      aebodsys = "Body System of Event"

      aedecod  = "Preferred Term for Event"

      aerel    = "Relatedness: 1=not,2=possibly,3=probably"

      aesev    = "Severity/Intensity:1=mild,2=moderate,3=severe";     

input subjid 1-3 aerel 5 aesev 7 aebodsys $ 9-34 aedecod $ 38-62;

datalines;

101 1 1 Cardiac disorders            Atrial flutter

101 2 1 Gastrointestinal disorders   Constipation

102 2 2 Cardiac disorders            Cardiac failure

102 1 1 Psychiatric disorders        Delirium

103 1 1 Cardiac disorders            Palpitations

103 1 2 Cardiac disorders            Palpitations

103 2 2 Cardiac disorders            Tachycardia

115 3 2 Gastrointestinal disorders   Abdominal pain

115 3 1 Gastrointestinal disorders   Anal ulcer

116 2 1 Gastrointestinal disorders   Constipation

117 2 2 Gastrointestinal disorders   Dyspepsia

118 3 3 Gastrointestinal disorders   Flatulence

119 1 3 Gastrointestinal disorders   Hiatus hernia

130 1 1 Nervous system disorders     Convulsion

131 2 2 Nervous system disorders     Dizziness

132 1 1 Nervous system disorders     Essential tremor

135 1 3 Psychiatric disorders        Confusional state

140 1 1 Psychiatric disorders        Delirium

140 2 1 Psychiatric disorders        Sleep disorder

141 1 3 Cardiac disorders            Palpitations

;

run;

/* NOTE: THE FULL MACRO CODE IS AT THE BOTTOM OF THIS POST */

/***  Call the macro  ***/
%inc 'D:\Documents and Settings\Desktop\Reusable Macros\AE Summary Macro.sas';

%aesum(aedsn=ae, safdsn=treat, grdvar=aesev, soc=aebodsys, pref=aedecod, subjid=subjid, minpercent=-1, outdsn=aesum, displaysoc=Y);

/***  Organize output  ***/

options formchar="|____|+|___+=|_/\<>*" pageno=1 nonumber nodate orientation=portrait center;

ods listing close;

ods rtf file="D:\Documents and Settings\dbateman\Desktop\AE Table.rtf" style=journal;

ods escapechar='~';

%let user=%sysfunc(upcase(&sysuserid.));

%let proddt=%sysfunc(date(),worddate.);

title1 j=left font='Arial' 'ABC Company, Inc.' j=right "Page ~{pageof}";

title2 j=left font='Arial' "Protocol: AZ1234";

footnote1 j=left font='Arial' "Program: AE_Example.sas by &user." j=right "Date Produced: &proddt.";

proc report data=aesum nowindows split='^' style(column)={asis=on}

            style(report)={font=('Arial',9.5pt,bold italic) pretext="Treatment Emergent Adverse Events"};

      column header by1_grd0 by1_grd1 by1_grd2 by1_grd3 aedecod;

      define header / display left 'System Organ Class/^Preferred Term' style=[CellWidth=30%];

      define by1_grd0 / display center ' ' style=[CellWidth=10%];

      define by1_grd1 / display center ' ' style=[CellWidth=10%];

      define by1_grd2 / display center ' ' style=[CellWidth=10%];

      define by1_grd3 / display center ' ' style=[CellWidth=10%];

      define aedecod / noprint;  /*  Must include this in order to identify correct rows to bold in next step  */

      compute aedecod;

        if aedecod=' ' then do;  /*  If system organ class is listed, then bold that row  */

           call define(_row_, "style", "style=[font_weight=bold]");

        end;

      endcomp;

run;

ods rtf close;

ods listing;

title; footnote;

OUTPUT

MACRO CODE


%macro aesum(aedsn=,whr=%str(1=1),safdsn=,byvar=,grdvar=,soc=,pref=,subjid=,minpercent=-1,outdsn=,displaysoc=Y);

      /******************************************************/

      /***  Error Messages for Required Input Parameters  ***/

      /******************************************************/

      %if %length(&aedsn.)=0 %then %do;

            %put ERROR: The AESUM macro requires the AEDSN dataset for execution.;

            %return;

      %end;

      %if %length(&safdsn.)=0 %then %do;

            %put ERROR: The AESUM macro requires the SAFDSN dataset for execution.;

            %return;

      %end;

      %if %length(&soc.)=0 %then %do;

            %put ERROR: The AESUM macro requires the SOC variable for execution.;

            %return;

      %end;

      %if %length(&pref.)=0 %then %do;

            %put ERROR: The AESUM macro requires the PREF variable for execution.;

            %return;

      %end;

      %if %length(&subjid.)=0 %then %do;

            %put ERROR: The AESUM macro requires the SUBJID variable for execution.;

            %return;

      %end;

      %if %length(&outdsn.)=0 %then %do;

            %put ERROR: The AESUM macro requires the OUTDSN dataset for execution.;

            %return;

      %end;

      /***  Set up AEDSN to not influence original data ***/

      data aedsn;

            set &aedsn.;

      run;

      /*********************************************/

      /***  Manipulate Stratification Variables  ***/

      /*********************************************/

      /* If no GRDVAR stratification variable, then end program */

      %if %length(&grdvar.)=0 %then %do;

            %put ERROR: The AESUM macro requires a GRDVAR variable for execution.  If no GRDVAR variable is needed, create a dummy numeric variable with a single distinct value.;

            %return;

      %end;

            /* Format GRDVAR stratification variable */

            %else %do;

                  data _null_;

                        set aedsn end=last;

                        if last then do;

                              call symput ('grdvartp', vtype(&grdvar.));

                        end;

                  run;

                  %if &grdvartp.=N %then %do;

                        data aedsn;

                              set aedsn;

                              grdvar=&grdvar.;

                        run;

                  %end;

                        %else %do;

                              %put ERROR: The AESUM macro requires that GRDVAR be numeric for execution.;

                              %return;

                        %end;

            %end;

      /* Not interested in any BYVAR stratification variable */

      %if %length(&byvar.)=0 %then %do;

            data aedsn;

                  set aedsn;

                  byvar='1';

            run;

      %end;

            /* Format BYVAR stratification variable */

            %else %do;

                  data _null_;

                        set aedsn end=last;

                        if last then do;

                              call symput('byvarfmt', vformat(&byvar.));

                              call symput ('byvartp', vtype(&byvar.));

                        end;

                  run;

                  %if &byvarfmt.= %then byvarfmt=BEST.;

                  %if &byvartp.=N %then %do;

                        data aedsn;

                              set aedsn;

                              byvar=compress(put(&byvar.,&byvarfmt.),' .<(+&!$?*)>;^-=/,%|');

                        run;

                  %end;

                        %else %do;

                              data aedsn;

                                    set aedsn;

                                    byvar=compress(&byvar.,' .<(+&!$?*)>;^-=/,%|');

                              run;

                        %end;

            %end;

      /*******************************/

      /***  Create Count Datasets  ***/

      /*******************************/

      proc sql noprint;

            /* Safety population count */

            select count(distinct &subjid.) into :popcount from &safdsn.;

            /* Maximum grade of each preferred term */

            create table maxgrades as

            select *, max(grdvar) as maxgrd_pt

            from aedsn

            where &whr.

            group by &subjid., byvar, &pref.;

            /* Maximum grade of each soc */

            create table maxgrades2 as

            select *, max(grdvar) as maxgrd_soc

            from maxgrades

            where &whr.

            group by &subjid., byvar, &soc., &pref.;

            /* Number of patients in each system organ class - across all grades */

            create table soccounts_allgrades as

            select unique byvar, &soc., count(unique(&subjid.)) as patcount0

            from maxgrades2

            where maxgrd_soc^=.

            group by byvar, &soc.

            order by &soc.;

            /* Number of patients in each system organ class - separated by grade */

            create table soccounts_bygrade as

            select unique byvar, &soc., maxgrd_soc, count(unique(&subjid.)) as patcountbygrd

            from maxgrades2

            where maxgrd_soc^=.

            group by byvar, &soc., maxgrd_soc;

            /* Number of patients in each preferred term - across all grades */

            create table ptcounts_allgrades as

            select unique byvar, &soc., &pref., count(unique(&subjid.)) as patcount0

            from maxgrades2

            where maxgrd_pt^=.

            group by byvar, &soc., &pref.

            order by &soc., &pref.;

            /* Number of patients in each preferred term - separated by grade */

            create table ptcounts_bygrade as

            select unique byvar, &soc., &pref., maxgrd_pt, count(unique(&subjid.)) as patcountbygrd

            from maxgrades2

            where maxgrd_pt^=.

            group by byvar, &soc., &pref., maxgrd_pt;

            /* Number of patients in each preferred term, but with a missing grade */

            create table ptcounts_nograde as

            select unique byvar, &soc., &pref., maxgrd_pt, count(unique(&subjid.)) as patcountnogrd

            from maxgrades2

            where maxgrd_pt=. & &soc.^=' ' & &pref.^=' '

            group by byvar, &soc., &pref., maxgrd_pt

            order by &soc., &pref., maxgrd_pt;

            /* Store number of observations from ptcounts_nograde into macro variables */

            select nobs into :nogrdobs from sashelp.vtable where libname='WORK' & memname='PTCOUNTS_NOGRADE';

            %let nogrdobs=%sysfunc(compress(&nogrdobs.));

            /* Store number of distinct by-variables and distinct by-variables values into macro variables */

            select count(distinct byvar) into :nbyvar from aedsn;

            %let nbyvar=%sysfunc(compress(&nbyvar.));

            select distinct byvar into :byvar1-:byvar&nbyvar. from aedsn;

            /* Store number of distinct grades and distinct grades values into macro variables */

            select count(distinct grdvar) into :ngrdvar from aedsn;

            %let ngrdvar=%sysfunc(compress(&ngrdvar.));

            select distinct grdvar into :grdvar1-:grdvar&ngrdvar. from aedsn where grdvar^=.;

            %let grdvar0=0;

      quit;

      %put Number of By variables: &nbyvar.;

      %do i=1 %to &nbyvar.;

            %put &&byvar&i.;

      %end;

      /* Arrange all-grade SOC and PT counts into columns instead of rows */

      proc transpose data=soccounts_allgrades out=transsoc_allgrades prefix=by;

            id byvar;

            by &soc.;

            var patcount0;

      run;

      proc transpose data=ptcounts_allgrades out=transpt_allgrades prefix=by;

            id byvar;

            by &soc. &pref.;

            var patcount0;

      run;

      %if &nogrdobs.>0 %then %do;

            proc transpose data=ptcounts_nograde out=transpt_nograde prefix=nogrd_by;

                  id byvar;

                  by &soc. &pref.;

                  var patcountnogrd;

            run;

      %end;

      %do i=1 %to &nbyvar.;

            /*  Arrange by-grade SOC counts into columns instead of rows  */

            proc transpose data=soccounts_bygrade out=transsoc_bygrade&i. prefix=by&&byvar&i.._grd;

                  id maxgrd_soc;

                  by &soc.;

                  var patcountbygrd;

                  where compress(byvar,' .<(+&!$?*)>;^-=/,%|')="&&byvar&i.";

            run;

            /*  Arrange by-grade PT counts into columns instead of rows  */

            proc transpose data=ptcounts_bygrade out=transpt_bygrade&i. prefix=by&&byvar&i.._grd;

                  id maxgrd_pt;

                  by &soc. &pref.;

                  var patcountbygrd;

                  where compress(byvar)="&&byvar&i.";

            run;

      %end;

      /*  Combine all SOC and PT datasets  */

      data soccounts;

            merge transsoc_allgrades (drop=_NAME_ rename=(%do i=1 %to &nbyvar.; by&&byvar&i.=by&&byvar&i.._grd0 %end;))

                  %do i=1 %to &nbyvar.;

                        transsoc_bygrade&i. (drop=_NAME_)

                  %end;;

            by &soc.;

      run;

      data ptcounts;

            merge transpt_allgrades (drop=_NAME_ rename=(%do i=1 %to &nbyvar.; by&&byvar&i.=by&&byvar&i.._grd0 %end;))

                  %do i=1 %to &nbyvar.;

                        transpt_bygrade&i. (drop=_NAME_)

                  %end;

                  %if &nogrdobs.>0 %then %do;

                        transpt_nograde (drop=_NAME_)

                  %end;;

            by &soc. &pref.;

            %do i=1 %to &nbyvar.;

                  %if &nogrdobs.>0 %then %do;

                        if nogrd_by&&byvar&i.^=. then do;

                              by&&byvar&i.._grd0=by&&byvar&i.._grd0 + nogrd_by&&byvar&i.;

                              &pref.='*'||&pref.;  /* Identify any preferred term where there was a missing grade */

                        end;

                  %end;;

            %end;

      run;

      proc sql noprint;

            select count(&pref.) into :ttlpats from ptcounts;  /* Total number of patients */

            select count(&pref.) into :nogradecnt from ptcounts   where substr(&pref.,1,1)='*';  /* Total number of PTs with a missing grade */

      quit;

      %if &popcount.=. | &ttlpats.=0 %then %let N=0;  /* If there are no qualifying patients, list population count as '0' ... */

            %else %let N=%sysfunc(compress(&popcount.));  /* ... otherwise, report it as-is */ 

      data allcounts;

            set soccounts ptcounts;

            by &soc.;

            i+1;

            %if &ttlpats.=0 %then %do;  /* If there are no qualifying patients ... */

                  if i>1 then delete;  /* ... then keep first entry as 'dummy row' for output */

            %end;

                  %else %do;  /* Otherwise, if there are qualifying patients ... */

                        if first.&soc. & last.&soc. then delete;  /*  ... then only keep entries where preferred terms are listed */

                  %end;

      run;

      data &outdsn.;

            length header $150;

            set allcounts;

            %if &ttlpats.=0 %then %do;

                  header='No qualifying patients';  /*  If there are no qualifying patients, then indicate that in report  */

            %end;

                  %else %do;  /*  If there is at least one qualifying patient, then produce the output accordingly  */

                        if &pref.=' ' then header=&soc.;  /*  If preferred term is missing, then list system organ class ...  */

                              else header='    '||&pref.;  /*  ... otherwise, list preferred term  */

                        /* Assign any missing counts to 0 to avoid undefined percents below */

                        %do i=1 %to &nbyvar.;

                              %do j=0 %to &ngrdvar.;

                                    if by&&byvar&i.._grd&&grdvar&j.=. then by&&byvar&i.._grd&&grdvar&j.=0;

                              %end;

                        %end;

                        %if &displaysoc.=N %then %do;  /* For IB output, do not list system organ class counts & percentages */

                              if &pref.^=' ' then do;

                                    %do i=1 %to &nbyvar.;

                                          %do j=0 %to &ngrdvar.;

                                                Cby&&byvar&i.._grd&&grdvar&j.=compress(put(by&&byvar&i.._grd&&grdvar&j.,best.)) || ' (' || compress(put(by&&byvar&i.._grd&&grdvar&j./&N.,percent9.1)) || ')';

                                          %end;

                                    %end;

                              end;

                        %end;

                              %else %do;  /* For AR output, list all counts & percentages */

                                    %do i=1 %to &nbyvar.;

                                          %do j=0 %to &ngrdvar.;

                                                Cby&&byvar&i.._grd&&grdvar&j.=compress(put(by&&byvar&i.._grd&&grdvar&j.,best.)) || ' (' || compress(put(by&&byvar&i.._grd&&grdvar&j./&N.,percent9.1)) || ')';

                                          %end;

                                    %end;

                              %end;

                  %end;

            /*  Only keep records that meet the minimum percentage requirement  */

            %do i=1 %to &nbyvar.;

                  if (by&&byvar&i.._grd0/&N.)*100<&minpercent. then _&i.=1;

            %end;

            if sum(of _1-_&nbyvar.)=&nbyvar. then delete;

            %do i=1 %to &nbyvar.;

                  %do j=0 %to &ngrdvar.;

                        drop by&&byvar&i.._grd&&grdvar&j.;

                        rename Cby&&byvar&i.._grd&&grdvar&j.=by&&byvar&i.._grd&&grdvar&j.;

                  %end;

            %end;

            if header=' ' then delete;

            drop i _1-_&nbyvar.;

      run;

      proc sort data=&outdsn. nodup;

            by &soc. &pref.;

      run;

      proc datasets lib=work;

            delete maxgrades maxgrades2 soccounts_allgrades soccounts_bygrade ptcounts_allgrades ptcounts_bygrade ptcounts_nograde

                  transsoc_allgrades transpt_allgrades transpt_nograde ptcounts soccounts allcounts

                  %do i=1 %to &nbyvar.;

                        transsoc_bygrade&i. transpt_bygrade&i.

                  %end;;

      run;

      quit;

%mend aesum;