Complex tables in SAS - How to switch the statistic depending on variable

[jlb623]

Basically, I am looking for a way to create the below table in SAS. I have been attempting with PROC TABULATE but have not been able to find a way to switch between statistics (i.e., N vs. median) depending on the variable. I'd appreciate any and all help with this!

Variable	Statistics
TREATMENT PERIOD
Before Study Assigned Drug	N
Patients with infection	n (%)
Number of infections per patient	median (range)
infection 1 (Insert)	n (%)
infection 2 (insert)	n (%)
infection 3 (insert)	n (%)
During Study Assigned Drug	N
Patients with infection	n (%)
Number of infections per patient	median (range)
infection 1 (insert)	n (%)
infection 2 (insert)	n (%)
infection 3 (insert)	n (%)
FOLLOW-UP (30-Days Post Study Drug)	N
Number of Patients with infection	n (%)
Number of infections per patient	median (range)
infection1 (insert)	n (%)
infection2 (insert)	n (%)
infection 3 (insert)	n (%)

[andreas_lds]

Please post the code you have and data in usable form, making it easier for us to understand the issue properly.

[ballardw]

You are going to have to explain a bit more about what "infection 1" etc actually mean. If you mean "exactly one infection" then for report procedures you likely need a second variable with the same values as the number of infections as the role of a variable to create median, or mean/max/min/std/range/qrange etc., as a VAR variable will preclude use of the same variable as a Class variable (using each level).

So example data is starting point in the form of data step code if at all possible to show what you have.

Tabulate will allow differing statistics but not in columns as you show so the n and percent would have to be on rows within a class variable with the row headings you show. To get something that looks exactly like you indicate will likely involve a summary step, some manipulation in a data step and then displaying the results.

[FreelanceReinh]

Hello @jlb623 and welcome to the SAS Support Communities!

Sorry for the late reply. My approach would rather look like this:

1. Compute the statistics in one or more PROC SQL / SUMMARY / FREQ / ... steps.
2. Use a DATA step to combine the results in a dataset resembling the summary table to be produced.
3. Use PROC REPORT to create the table from that dataset.

"Switching between statistics" is then as simple as printing a list of character strings.

Here is an example (but the data structure may be different from yours):

Spoiler

/* Create sample data for demonstration */

data have;
call streaminit(27182818);
do patno=1 to 200;
  do period=1 to 3;
    do _n_=1 to rand('poisson',1/period**2);
      aept=byte(rand('integer',65,67));
      output;
    end;
    if rand('bern',0.02) then leave;
    if _n_=1 then do;
      aept=' ';
      output;
    end;
  end;
end;
run;

proc format;
value period
1='Before Study Assigned Drug'
2='During Study Assigned Drug'
3='FOLLOW-UP (30-Days Post Study Drug)';
run;

/* Compute descriptive statistics */

proc sql;
create table periods as
select period, count(distinct patno) as npat,
       count(distinct case when aept=' ' then . else patno end) as npatinf,
       calculated npatinf/calculated npat as pctpatinf
from have
group by period;

create table patients as
select period, patno, sum(aept>' ') as ninf
from have
group by period, patno;

create table infections as
select period, aept, count(distinct patno) as npat_aept,
       calculated npat_aept/(select npat from periods where period=a.period) as pct_aept
from have a
where aept ne ' '
group by period, aept;
quit;

proc summary data=patients;
by period;
var ninf;
output out=medrange median= min= max= / autoname;
run;

/* Prepare input dataset for PROC REPORT */

data inftab;
length item $50 stat $20;
set periods(in=p)
    medrange(in=m)
    infections;
by period;
if p then do;
  item=put(period,period.);
  stat=put(npat, 3.);
  output;
  item='Patients with infection';
  stat=put(npatinf, 3.)||' '||put(-pctpatinf,percent8.1);
  output;
end;
else if m then do;
  item='Number of infections per patient: median (range)';
  stat=put(ninf_median,3.)||' ('||catx('-',ninf_min,ninf_max)||')';
  output;
end;
else do;
  item='  '||aept;
  stat=put(npat_aept, 3.)||' '||put(-pct_aept,percent8.1);
  output;
end;
run;

/* Create the summary table (ODS destination: Listing) */

proc report data=inftab;
column period item stat;
define period / order noprint;
define item / 'TREATMENT PERIOD';
define stat / ' ';
break before period / skip;
run;

Output:

TREATMENT PERIOD

Before Study Assigned Drug                          200
Patients with infection                             120 ( 60.0%)
Number of infections per patient: median (range)      1 (0-4)
  A                                                  52 ( 26.0%)
  B                                                  49 ( 24.5%)
  C                                                  47 ( 23.5%)

During Study Assigned Drug                          194
Patients with infection                              44 ( 22.7%)
Number of infections per patient: median (range)      0 (0-2)
  A                                                  16 (  8.2%)
  B                                                  13 (  6.7%)
  C                                                  20 ( 10.3%)

FOLLOW-UP (30-Days Post Study Drug)                 192
Patients with infection                              25 ( 13.0%)
Number of infections per patient: median (range)      0 (0-2)
  A                                                  11 (  5.7%)
  B                                                   9 (  4.7%)
  C                                                   8 (  4.2%)