In PROC REPORT, is there a way to align summary statistics of different lengths by decimal point?

[ble9245]

I am working on making a table of summary statistics using PROC REPORT and ODS RTF to format the values. One variable is numeric and summarized using mean, while the other variables are categorical and are summarized using n, (%). What I have is a SAS dataset that contains the table with calculated summary statistics, and then I use PROC REPORT to format the table and use ODS RTF to output the file. Below is some sample code of generating some summary stats on the CARS dataset, and then using PROC REPORT to format it. I included 3 variables (MPG, Type and Origin).

/* Get summary stats of MPG variable */
proc summary data=sashelp.CARS nway;
var MPG_CITY;
output out=MPG_SUMMARY
mean=_MEAN;
run;

data MPG_STATS;
set MPG_SUMMARY;
format STAT SUM_STAT $50.;
STAT = 'MPG (Mean)';
SUM_STAT = put(_MEAN, 6.1);
keep STAT SUM_STAT;
run;


/* Get summary stats of TYPE */
proc freq data=sashelp.CARS noprint;
tables TYPE / out=TYPE_COUNT;
run;

data TYPE_STATS;
set TYPE_COUNT;
length STAT SUM_STAT $50.;
if TYPE = 'SUV';
STAT = 'Type (SUV), n (%)';
SUV_PCT = put(PERCENT, 4.1);
SUM_STAT = compress(COUNT) || ' (' || compress(SUV_PCT) || '%)';
keep STAT SUM_STAT;
run;


/* Get summary stats of ORIGIN */
proc freq data=sashelp.CARS noprint;
tables ORIGIN / out=ORIGIN_COUNT;
run;

data ORIGIN_STATS;
set ORIGIN_COUNT;
length STAT SUM_STAT $50.;
if ORIGIN = 'USA';
STAT = 'Origin (USA), n (%)';
USA_PCT = put(PERCENT, 4.1);
SUM_STAT = compress(COUNT) || ' (' || compress(USA_PCT) || '%)';
keep STAT SUM_STAT;
run;

/* Concatenate summary stats datasets */
data CAR_STATS;
set 
MPG_STATS
TYPE_STATS
ORIGIN_STATS;
run;


/* PROC REPORT */
options orientation=portrait
topmargin=1in bottommargin=1in leftmargin=1in rightmargin=1in;

ods rtf file = 'C:\Desktop\CARS Summary Stats Table.rtf' bodytitle;

proc report data=CAR_STATS nowd 

style(report) = {just=center cellpadding=3pt cellspacing=0pt 
frame=above rules=groups 
bordercollapse=separate borderstyle=solid}

style(lines)={background=white font_face="calibri" font_size=10pt just=center}

style(header) = {font=("calibri", 10pt) background=white fontweight=bold}
style(column) = {font=("calibri", 10pt)}

headline headskip split='|';

column (STAT SUM_STAT);

define STAT / 'Variable'
style(header) = {just=left verticalalign=middle bordertopwidth=0.1mm borderbottomwidth=0.1mm} 
style(column) = {just=left cellwidth=1.5in};

define SUM_STAT / "Total | (n=428)"
style(header) = {just=center bordertopwidth=0.1mm borderbottomwidth=0.1mm}
style(column) = {just=d width=1in};

title1 font="calibri" height=10pt justify=center "Summary Stats for CAR dataset";

run; 

ods rtf close;

What I want to do is align/justify the summary stat values by decimal point, but the MPG mean value gets pulled to the right side in order to align the decimal point to the percentages of the categorical variables below. Also, the "Total (n=428) column looks off because it is justified in the center, whereas the values below are justified by decimal point (see screenshot below - this is the table/RTF file that my sample code above generates).

 

I'm wondering if there is anything I can do to center the MPG (Mean) variable, and have the "Total (n=428)" column centered to the values below too. This is what the table would ideally look like:

I achieved this by using PROC REPORT to justify all values in the center, and then manually going into the RTF file to add a space to the "Type (SUV)" value so the decimal point in the percentage would align with the value in the Origin (USA) variable. However, it would be ideal if I could find a way to have SAS generate this ideal table for me, since the table I am working on has so many additional variables.

Is it possible to generate this ideal table with the code I have now? I have tried modifying the just= parts in different places in my code, but no success. Or, do I perhaps have to use PROC REPORT in a different way?

Any help or advice would be greatly appreciated. Thank you in advance!

[Ksharp]

Just change
style(column) = {just=d width=1in};

into
style(column) = {just=center width=1in};

[ble9245]

Hi KSharp,

Thank you for your reply! So when I change just=d to just=center, the decimal point in 60 (14.0%) for the "Type (SUV), n (%)" row does not align with the 147 (34.3%) "Origin (USA), n (%)" row because the values are different lengths. I had to open the RTF file and manually add a space to the "60 (14.0%)" to get the decimal points to align, but I am wondering if there is a way to use code to have SAS/PROC REPORT automatically format, center and align the values the way I want.

[Ksharp · Accepted solution]

OK. How about this one ?

/* PROC REPORT */
options orientation=portrait
topmargin=1in bottommargin=1in leftmargin=1in rightmargin=1in;

ods rtf file = 'C:\temp\temp.rtf' bodytitle;

proc report data=CAR_STATS nowd 

style(report) = {just=center cellpadding=3pt cellspacing=0pt 
frame=above rules=groups 
bordercollapse=separate borderstyle=solid}

style(lines)={background=white font_face="calibri" font_size=10pt just=center}

style(header) = {font=("calibri", 10pt) background=white fontweight=bold}
style(column) = {font=("calibri", 10pt)}

headline headskip split='|';

column (STAT SUM_STAT);

define STAT / 'Variable'
style(header) = {just=left verticalalign=middle bordertopwidth=0.1mm borderbottomwidth=0.1mm} 
style(column) = {just=left cellwidth=1.5in};

define SUM_STAT / "Total | (n=428)"
style(header) = {just=center bordertopwidth=0.1mm borderbottomwidth=0.1mm}
style(column) = {just=c width=1in}; /*<-----*/

title1 font="calibri" height=10pt justify=center "Summary Stats for CAR dataset";


 /*<-----*/
compute SUM_STAT;
if not find(STAT,'Mean') then call define(_col_,'style','style={just=dec}');
endcomp;

run; 

ods rtf close;

[ble9245]

Hi KSharp,

Apologies for the delayed response and thank you for your help! This solution worked - it's good to know you can use if statements in compute blocks to format certain columns how you want. 

Thanks again and happy holidays!