topic Re: Style template for table with count data in rows and binary outcome in columns with N (%) ? in ODS and Base Reporting

Style template for table with count data in rows and binary outcome in columns with N (%) ?

PharmlyDoc — Thu, 12 Aug 2021 17:31:14 GMT

I am using proc tabulate to create a table, though maybe I should be using proc report instead.

I have provided a dataset and code with a screenshot of proc tabulate output. I have also provided a screenshot of what I want the table to look like (created in Adobe Illustrator).

Data readmissions ;
infile datalines delimiter="," ;
input PAT_ID DischargeDisposition : $17. Cohort : $17. FacilitySize : $13. InsuranceType : $12. Age ICDCounts ED_Visits OP_Visits LengthOfStay Readmission @@;
datalines;
9999196,Discharge to Home,Cardiovascular,Over 500 Beds,Medicare,46,249,3,5,7,1
9997285,Discharge to SNF,Medicine,Over 500 Beds,Medicare,54,236,4,6,5,1
9995412,Discharge to Home,Surgery,Over 500 Beds,Managed Care,88,219,6,6,7,1
9994014,Discharge to SNF,Medicine,Over 500 Beds,Managed Care,73,186,6,3,7,1
9993883,Discharge to SNF,Surgery,Over 500 Beds,Managed Care,46,267,3,3,5,1
9993034,Discharge to SNF,Neurology,200-500 Beds,Medicare,53,281,5,4,6,1
9992793,Discharge to Home,Surgery,Over 500 Beds,Medicare,88,189,6,4,6,1
9992476,Discharge to Home,Surgery,Over 500 Beds,Managed Care,22,267,5,7,6,1
9992005,Discharge to Home,Medicine,Over 500 Beds,Medicare,32,280,4,7,6,1
9991997,Discharge to SNF,Medicine,200-500 Beds,Medicare,22,76,4,6,7,1
9991725,Discharge to SNF,Medicine,Over 500 Beds,Other,44,245,3,4,7,1
9990172,Discharge to SNF,Cardiorespiratory,200-500 Beds,Managed Care,28,122,6,5,6,1
9989261,Discharge to Home,Surgery,Over 500 Beds,Other,65,264,5,4,6,1
9989024,Discharge to Home,Cardiovascular,Over 500 Beds,Medicare,87,202,3,3,7,1
9988168,Discharge to Home,Surgery,Over 500 Beds,Medicaid,81,247,4,3,6,1
9988079,Discharge to SNF,Surgery,200-500 Beds,Managed Care,83,167,2,7,7,1
9987993,Discharge to Home,Surgery,Over 500 Beds,Managed Care,61,281,3,4,7,1
9987909,Discharge to SNF,Cardiovascular,200-500 Beds,Medicare,56,125,4,7,7,1
9987120,Discharge to Home,Cardiorespiratory,Over 500 Beds,Medicare,35,213,2,6,7,1
9986356,Discharge to Home,Cardiovascular,Over 500 Beds,Medicare,65,234,4,5,6,1
9979847,Discharge to Home,Medicine,Over 500 Beds,Managed Care,49,194,6,6,6,1
9979178,Discharge to SNF,Surgery,Over 500 Beds,Medicaid,90,79,3,5,5,1
9978674,Discharge to Home,Surgery,Over 500 Beds,Managed Care,91,264,2,3,6,1
9978628,Discharge to Home,Medicine,Over 500 Beds,Medicare,69,258,4,4,6,1
9972504,Discharge to SNF,Medicine,200-500 Beds,Managed Care,58,281,5,6,7,1
9972418,Discharge to Home,Neurology,Over 500 Beds,Medicaid,91,232,5,7,6,1
9967569,Discharge to SNF,Medicine,Up to 200 Beds,Medicare,44,161,4,5,7,1
9966829,Discharge to SNF,Medicine,Over 500 Beds,Medicare,29,270,2,6,5,1
9966725,Discharge to Home,Surgery,Over 500 Beds,Managed Care,24,277,4,3,6,1
9966284,Discharge to Home,Surgery,Over 500 Beds,Medicaid,29,275,1,2,6,0
9965369,Discharge to Hospice,Medicine,200-500 Beds,Medicare,60,111,3,4,7,0
9965221,Discharge to SNF,Cardiovascular,200-500 Beds,Managed Care,28,251,4,4,6,0
9963889,Discharge to SNF,Medicine,200-500 Beds,Medicare,77,141,2,4,7,0
9963695,Discharge to SNF,Medicine,Over 500 Beds,Managed Care,67,68,2,1,6,0
9962202,Discharge to Home,Cardiorespiratory,Over 500 Beds,Medicare,74,265,3,1,6,0
9961073,Discharge to Home,Surgery,Over 500 Beds,Medicare,26,272,3,1,7,0
9958404,Discharge to SNF,Neurology,Over 500 Beds,Managed Care,88,171,2,3,5,0
9957855,Discharge to Home,Medicine,200-500 Beds,Medicare,93,184,2,2,7,0
9955669,Discharge to Hospice,Surgery,200-500 Beds,Medicaid,46,174,4,4,7,0
9953519,Discharge to SNF,Neurology,200-500 Beds,Medicare,35,210,4,1,7,0
9952608,Discharge to Home,Surgery,Over 500 Beds,Managed Care,59,216,3,2,7,0
9952244,Discharge to Home,Medicine,Over 500 Beds,Managed Care,78,243,2,3,6,0
9951467,Discharge to SNF,Surgery,Over 500 Beds,Medicaid,72,240,2,3,6,0
9951073,Discharge to Home,Surgery,Over 500 Beds,Medicare,22,228,4,2,6,0
9949407,Discharge to Home,Medicine,Over 500 Beds,Managed Care,79,232,2,1,7,0
9947877,Discharge to SNF,Medicine,200-500 Beds,Medicare,37,281,3,2,5,0
9947761,Discharge to SNF,Surgery,Up to 200 Beds,Medicare,63,203,4,2,6,0
9946379,Discharge to Home,Surgery,Over 500 Beds,Managed Care,38,241,3,1,6,0
9945349,Discharge to Home,Medicine,Over 500 Beds,Medicaid,61,257,3,4,7,0
9944394,Discharge to Rehab,Surgery,Over 500 Beds,Medicaid,59,245,2,2,7,0
9943320,Discharge to Home,Medicine,Over 500 Beds,Medicaid,48,218,2,3,6,0
9940882,Discharge to Home,Surgery,Over 500 Beds,Medicare,36,280,4,1,7,0
9940664,Discharge to Hospice,Surgery,200-500 Beds,Medicaid,89,141,4,2,6,0
9940109,Discharge to Home,Surgery,Over 500 Beds,Medicare,54,258,2,2,7,0
9939758,Discharge to Home,Surgery,Over 500 Beds,Other,67,213,3,2,7,0
9939067,Discharge to Home,Surgery,Over 500 Beds,Workers Compensation,86,183,3,2,7,0
9936455,Discharge to Home,Medicine,Over 500 Beds,Medicare,36,206,1,1,7,0
9936153,Discharge to SNF,Neurology,Over 500 Beds,Managed Care,42,148,1,2,6,0
9935085,Discharge to SNF,Medicine,Over 500 Beds,Managed Care,64,179,3,1,7,0
;
run;




proc format;
invalue InReadmission
  1 = 1
  0 = 2
;
run;

data readmissions;
set readmissions;
Readmission = input(Readmission,InReadmission.);
run;

proc format;
value Readmissionf
1 = 'Yes'
2 = 'No' ;
run;

proc format;
picture pctfmt (round)
    low-high = '(009.99%)'
    ( prefix = '(' );
run;



ods html  path="%sysfunc(getoption(work) )"  style=Styles.Journal2 ; 
title "Patient's Healthcare Utilization.";
proc tabulate data=readmissions order=internal;
format Readmission  Readmissionf.;
keylabel Sum=' ' ;
keylabel RowPCTSum='N (%) ';
keyword RowPCTSum / style=[just=L];
class Readmission;
var ED_Visits OP_Visits LengthOfStay;
table (ED_Visits OP_Visits LengthOfStay),
 Readmission*(Sum*f=8.0 rowpctsum*f=pctfmt.) ALL*(Sum*f=8.0) ;
run; Readmissionfmt
ods html close;

proc tabulate output

The table below is what I want. But ignore the grey color of the text for rows ED_Visits and LengthOfStay, which is a side-effect from adding the transparent grey rectangles in Adobe Illustrator to simulate row shading.

what I want the table to look like

Re: Style template for table with count data in rows and binary outcome in columns with N (%) ?

JeffMeyers — Thu, 12 Aug 2021 17:59:43 GMT

I saw your question on the TABLEN page, but unfortunately I don't have a straightforward way to do this with that macro.

Here's how I would make it using PROC REPORT. I don't know of any special styles that would make it exactly how you want without a bit of programming like this:

proc sql;
    %macro loop(varlist,labels);
        create table readm as
            %do i = 1 %to %sysfunc(countw(&varlist,%str( )));
                %if &i>1 %then %do; OUTER UNION CORR %end;
                select "%qscan(%superq(labels),&i,|)" as label,
                    sum(ifn(readmission=1,%scan(&varlist,&i,%str( )),0)) as n1,100*calculated n1/sum(%scan(&varlist,&i,%str( ))) as pct1,
                    strip(put(calculated n1,12.0))||' ('||strip(put(calculated pct1,12.1))||'%)' as n_pct1,
                    sum(ifn(readmission=2,%scan(&varlist,&i,%str( )),0)) as n2,100*calculated n2/sum(%scan(&varlist,&i,%str( ))) as pct2,
                    strip(put(calculated n2,12.0))||' ('||strip(put(calculated pct2,12.1))||'%)' as n_pct2,
                    sum(%scan(&varlist,&i,%str( ))) as total
                    from readmissions
            %end;;
            select * from readm;
    %mend;
    %loop(ed_visits op_visits lengthofstay,ED Visits|OP Visits|Length of Stay);
quit;

proc report data=readm nowd split='~'
    style(column)={color=black
                   backgroundcolor = white
                   bordercolor = white
                   borderstyle = none}
    style(lines)={color=black
                   backgroundcolor = white
                   bordercolor = white
                   borderstyle = none}
    style(header)={background=white 
                   color=black
                   bordercolor = white
                   borderstyle = none}
    style(report)={color=black
                   cellpadding = 0
                   borderspacing = 0
                   cellspacing=0
                   frame = void
                   rules = groups
                   bordercollapse = separate
                   borderleftstyle = none
                   borderrightstyle = none
                   bordertopstyle = none
                   borderbottomstyle = none };
    
    columns (label ('Readmission' n_pct1 n_pct2) total);
    
    define label / display 'A0'x left style={fontweight=bold};
    define n_pct1 / display 'Yes~N (%)' center style={cellwidth=1in};
    define n_pct2 / display 'No~N (%)' center style={cellwidth=1in};
    define total / display 'Total~N' center style={cellwidth=1in};

    compute total;
        shade+1;
        if shade=1 then call define(_row_,'style/merge','style={bordertopstyle=solid bordertopcolor=black bordertopwidth=1');
        if mod(shade,2)=0 then call define(_Row_,'style/merge','style={background=greyef}');
        
    endcomp;
    compute after/ style={bordertopstyle=solid bordertopwidth=1 bordertopcolor=black};
        line @1 ' ';
    endcomp;
    compute before _page_/ style={borderbottomstyle=solid borderbottomwidth=1 borderbottomcolor=black};
        line @1 ' ';
    endcomp;    
run;

A caveat is that it might look different in different ODS destinations since they all have their own style quirks.

Re: Style template for table with count data in rows and binary outcome in columns with N (%) ?

PharmlyDoc — Thu, 12 Aug 2021 20:06:24 GMT

Awesome!

It worked flawlessly in SAS 9.4 in Windows.

For it to work in SAS Studio I needed to change 'A0'x to '0A'x.

Otherwise, using 'A0'x in SAS Studio returns:

ERROR: Invalid characters were present in the data.

ERROR: An error occurred while processing text data.

I was going to ask how to produce a left-aligned title that sits above Readmission,

but I figured it out by editing the "compute before _page_" statement like so:

compute before _page_/ style={borderbottomstyle=solid borderbottomwidth=1 borderbottomcolor=black};
       line @1 ' ';
       line @1 "Patient's Healthcare Utilization.";
    endcomp;    
run;

Re: Style template for table with count data in rows and binary outcome in columns with N (%) ?

JeffMeyers — Thu, 12 Aug 2021 21:05:32 GMT

The 'A0'x is a Unicode unbreakable space character. Not all fonts support it so I'm guessing that's why your SAS Studio didn't like it. The alternative would be to do as you did ('OA'x is a tab space) or to establish an escape character (ods escapechar='^';) and then do '^_' instead.

For the title you don't need the first line statement (line @1 ' ';) if you are adding the title. I inserted a blank line there to add the line below, but if you replace it with text it will do the same thing. With having two line statements you'll have a blank line in the box above your title (which doesn't really hurt, but you don't need it if you don't want it).