Help with PROC TABULATE Formatting

[hr667]

Hello, I'm trying to format a table in a report I am programing. I'm having some issues that I haven't been able to work through on my own, and I was wondering if there even is a way to do what I'm trying to do!

Below are some sample data and my current PROC TABULATE step.

data table1;
input sn q1n q2n arm age_cat patient_gender patient_pcp;
label 
sn = "Screened"
q1n = "Eligible"
q2n = "Consented"
age_cat = "Age"
patient_pcp = "Has Primary Care Physician"
patient_gender = "Gender Identity"
;
CARDS;
1	1	1	1	1	1	1
1	1	1	1	1	1	1
1	1	1	1	2	1	1
1	1	1	1	2	1	1
1	1	1	1	2	2	1
1	1	1	1	2	2	1
1	1	1	1	3	2	1
1	1	1	1	3	2	1
1	1	1	1	3	2	2
1	1	1	1	4	2	2
1	0	0	1	0	0	0
1	0	0	1	0	0	0
1	0	0	1	0	0	0
1	0	0	1	0	0	0
1	0	0	1	0	0	0
1	0	0	1	0	0	0
1	0	0	1	0	0	0
1	0	0	1	0	0	0
1	0	0	1	0	0	0
1	0	0	1	0	0	0
;
run;

data class3;
input arm age_cat patient_gender patient_pcp;
label 
age_cat = "Age"
patient_pcp = "Has Primary Care Physician"
patient_gender = "Gender Identity"
;
CARDS;
1	1	1	1
1	1	1	2
1	1	1	3
1	1	2	1
1	1	2	2
1	1	2	3
1	1	3	1
1	1	3	2
1	1	3	3
1	1	4	1
1	1	4	2
1	1	4	3
1	1	5	1
1	1	5	2
1	1	5	3
1	2	1	1
1	2	1	2
1	2	1	3
1	2	2	1
1	2	2	2
1	2	2	3
1	2	3	1
1	2	3	2
1	2	3	3
1	2	4	1
1	2	4	2
1	2	4	3
1	2	5	1
1	2	5	2
1	2	5	3
1	3	1	1
1	3	1	2
1	3	1	3
1	3	2	1
1	3	2	2
1	3	2	3
1	3	3	1
1	3	3	2
1	3	3	3
1	3	4	1
1	3	4	2
1	3	4	3
1	3	5	1
1	3	5	2
1	3	5	3
1	4	1	1
1	4	1	2
1	4	1	3
1	4	2	1
1	4	2	2
1	4	2	3
1	4	3	1
1	4	3	2
1	4	3	3
1	4	4	1
1	4	4	2
1	4	4	3
1	4	5	1
1	4	5	2
1	4	5	3
1	5	1	1
1	5	1	2
1	5	1	3
1	5	2	1
1	5	2	2
1	5	2	3
1	5	3	1
1	5	3	2
1	5	3	3
1	5	4	1
1	5	4	2
1	5	4	3
1	5	5	1
1	5	5	2
1	5	5	3
1	6	1	1
1	6	1	2
1	6	1	3
1	6	2	1
1	6	2	2
1	6	2	3
1	6	3	1
1	6	3	2
1	6	3	3
1	6	4	1
1	6	4	2
1	6	4	3
1	6	5	1
1	6	5	2
1	6	5	3
2	1	1	1
2	1	1	2
2	1	1	3
2	1	2	1
2	1	2	2
2	1	2	3
2	1	3	1
2	1	3	2
2	1	3	3
2	1	4	1
2	1	4	2
2	1	4	3
2	1	5	1
2	1	5	2
2	1	5	3
2	2	1	1
2	2	1	2
2	2	1	3
2	2	2	1
2	2	2	2
2	2	2	3
2	2	3	1
2	2	3	2
2	2	3	3
2	2	4	1
2	2	4	2
2	2	4	3
2	2	5	1
2	2	5	2
2	2	5	3
2	3	1	1
2	3	1	2
2	3	1	3
2	3	2	1
2	3	2	2
2	3	2	3
2	3	3	1
2	3	3	2
2	3	3	3
2	3	4	1
2	3	4	2
2	3	4	3
2	3	5	1
2	3	5	2
2	3	5	3
2	4	1	1
2	4	1	2
2	4	1	3
2	4	2	1
2	4	2	2
2	4	2	3
2	4	3	1
2	4	3	2
2	4	3	3
2	4	4	1
2	4	4	2
2	4	4	3
2	4	5	1
2	4	5	2
2	4	5	3
2	5	1	1
2	5	1	2
2	5	1	3
2	5	2	1
2	5	2	2
2	5	2	3
2	5	3	1
2	5	3	2
2	5	3	3
2	5	4	1
2	5	4	2
2	5	4	3
2	5	5	1
2	5	5	2
2	5	5	3
2	6	1	1
2	6	1	2
2	6	1	3
2	6	2	1
2	6	2	2
2	6	2	3
2	6	3	1
2	6	3	2
2	6	3	3
2	6	4	1
2	6	4	2
2	6	4	3
2	6	5	1
2	6	5	2
2	6	5	3
; 
run;


proc format;
	picture pctf (round) other='009.9%';
	value armfmt  1 = "Pre-"				
				  2 = "Post-"; 
	value agefmt  1 = "18-29"
				  2 = "30-39"
				  3 = "40-49"
				  4 = "50-59"
				  5 = "60-69"
				  6 = ">=70";
	value genderfmt 1 = "Man"
					2 = "Woman"
					3 = "Non-binary/non-conforming"
					4 = "Other"
					5 = "Decline";
	value pcpfmt  1 = "Yes"
				  2 = "No"
				  3 = "Decline";
run;

proc tabulate data = table1 classdata = class3 order=data format=20. style={bordercolor=black background=white borderwidth=1 cellwidth=80 font=("Times New Roman", 7pt,Normal)};
class arm age_cat patient_gender patient_pcp/preloadfmt ORDER=DATA s={bordercolor=black background=white borderwidth=1 cellwidth=200 font=("Times New Roman", 7pt,Bold) just=c} ;
classlev arm age_cat patient_gender patient_pcp/ s={bordercolor=black background=white borderwidth=1 cellwidth=200 font=("Times New Roman", 7pt,Bold)};
var sn q1n q2n/ s={bordercolor=black background=white borderwidth=1 font=("Times New Roman", 7pt,Bold)};
table sn (q1n)*(sum*f=best12. colpctsum<sn>*f=pctf.) (q2n)*(sum*f=best12. colpctsum<q1n>*f=pctf.) (age_cat)*(n*f=best12. colpctn<q2n>*f=pctf.) (patient_gender)*(n*f=best12. colpctn<q2n>*f=pctf.) (patient_pcp)*(n*f=best12. colpctn<q2n>*f=pctf.), arm ALL='Total' /printmiss box={s={bordercolor=black background=white borderwidth=1}};
format arm armfmt. age_cat agefmt. patient_gender genderfmt. patient_pcp pcpfmt.;
keyword all sum colpctsum colpctn n/ s={background=white font=("Times New Roman", 7pt,Bold)};
keylabel n ='N' sum='N' colpctsum = '%' colpctn = '%'; 
label arm = 'Arm';
title j=left font = "Times New Roman" 'Table 1: Patient Screening & Demographic Information';
run;

The outputted table looks like this:

This is close to what I want, but I would like to remove the '0' rows which represent missing. I tried setting these values to missing, but the table would then only include those who were consented i.e. remove observations from the table that had missing values and make the screened value incorrect.

I also would like to add formatting to the box directly to the right of the variable labels. It is currently outputting shaded blue, and I would like to remove that to match the formatting on the rest of the table.

Any guidance would be appreciated, thank you!

[ChrisNZ]

Probably too late, but:

1. Help us help you by boiling down your problem. Hundreds of figures and multiple data sets are unneeded to build a cross table and explain what you want to achieve.

2. If you want the percentages not to reach 100%, you need to perform your custom calculations before hand and use proc tabulate to only display the results.

[hr667]

No not too late, but I only have one figure and two datasets (one of which is to just to make sure all columns/rows appear even if they're empty) . Not sure what the issue is with that.

I carefully laid all of it out so that the whole thing could be run and would directly output the single table I'm asking about. The PROC TABULATE portion is where my question is, but people always ask for example data in these forums so I thought I'd supply that.  

For your second point, I'm not sure what you mean. These are my questions about formatting PROC TABULATE output from my original post:

1. I would like to remove the '0' rows which represent missing. I tried setting these values to missing, but the table would then only include those who were consented i.e. remove observations from the table that had missing values and make the screened value incorrect.
2. I also would like to add formatting to the box directly to the right of the variable labels. It is currently outputting shaded blue, and I would like to remove that to match the formatting on the rest of the table.

Any help would be appreciated!

[sbxkoenk]

---

@hr667 wrote:

This is close to what I want, but I would like to remove the '0' rows which represent missing. I tried setting these values to missing, but the table would then only include those who were consented i.e. remove observations from the table that had missing values and make the screened value incorrect.

I also would like to add formatting to the box directly to the right of the variable labels. It is currently outputting shaded blue, and I would like to remove that to match the formatting on the rest of the table.

1.)  To remove the '0' rows :

• To the TABLE statement and after the forward slash ( / ) you add :
  printmiss MISSTEXT='0' ... or just ... printmiss
• To the PROC TABULATE statement you add : out=tabulardata.
• In the tabulardata dataset (to be found in WORK library) you can delete (programmatically with a WHERE statement) the rows you do not want to display and then run a 2nd PROC TABULATE. The 2nd PROC TABULATE is only displaying ... not summarizing / not aggregating / not calculating!
  Make sure the screened (non-zero) values stay identical to the ones in the original table. Check this diligently.

2.)  To get rid of the shaded blue next to the variable label :

• Proc TABULATE does NOT have a table template like (for example) PROC FREQ and PROC MEANS.
• But the 'look' of PROC TABULATE output is impacted by the STYLE= overrides in the procedure syntax. And you can change the style by means of a style template.

• ODS HTML, proc tabulate, proc template, how to change style?

  https://communities.sas.com/t5/ODS-and-Base-Reporting/ODS-HTML-proc-tabulate-proc-template-how-to-ch...

• PROC TABULATE – Building Tables with Style Michael Eberhart, MPH, Department of Public Health, Philadelphia, PA

  https://www.lexjansen.com/nesug/nesug10/hw/hw04.pdf

Ciao, Koen

[hr667]

Hi Koen(@sbxkoenk),

Thanks so much for your reply!

1) I've tried what you suggested. I've gotten to the point of removing the rows with the 0's that I don't want to display. I'm having some issues formatting the proc tabulate statement to display the outputted dataset from the first proc tabulate. Do you have any guidance on this?

2) Worked perfectly!

Thanks again!