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;
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.
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.
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.
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:
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;
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).
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.