topic Re: How to change the sub header styles in proc tabulate using a custom template? in ODS and Base Reporting

How to change the sub header styles in proc tabulate using a custom template?

SASAlex101 — Tue, 02 May 2023 22:55:28 GMT

I am trying to add boarders to the sub headers of the output of proc tabulate. I have created a custom style based off monochrome and I have some custom styling in the proc tabulate itself. The code I have:

data have;
length Name $10. Field1 $10. Field2 $10.;
infile datalines delimiter='#';
input Name $ Field1 $ Field2 $ N;
datalines;
Jim#stage1#Sub1#1
Steve#stage2#Sub2#1
Dave#stage2#Sub3#1
Jessica#stage2#Sub3#1
Bob#stage3#Sub3#1
Dan#stage2#Sub1#1
Jim#stage1#Sub1#1
Steve#stage1#Sub3#1
Dave#stage2#Sub2#1
Jessica#stage2#Sub2#1
Bob#stage2#Sub1#1
Dan#stage2#Sub3#1
;
run;



proc template;
  define style my_monospace;
    parent=styles.monospace;
class fonts /
      'docFont' = ("Trebuchet MS",8pt)
      'headingFont' = ("Trebuchet MS",9pt,Bold)
      'headingEmphasisFont' = ("Trebuchet MS",9pt,Bold)
      'FixedFont' = ("Trebuchet MS",8pt)
      'FixedHeadingFont' = ("Trebuchet MS",8pt)
      'FixedStrongFont' = ("Trebuchet MS",8pt)
      'FixedEmphasisFont' = ("Trebuchet MS",8pt)
      'EmphasisFont' = ("Trebuchet MS",8pt)
      'StrongFont' = ("Trebuchet MS",8pt)
      'TitleFont' = ("Trebuchet MS",10pt,Bold)
      'TitleFont2' = ("Trebuchet MS",10pt,Bold)
      'BatchFixedFont' = ("Trebuchet MS",8pt);
    class Header / 
      backgroundcolor=cxEEEEEE;
  end;
  end;
run;

proc template;
   source styles.monospace / expand;
run;


ods excel file="...somedirectory\test.xlsx";


    title1 j=center "Title1" ;
	title2 j=center "Title2" ;  
	title3 j=center "Title3" ;  
 
	ods excel options
	(
	sheet_name="Table" 
	sheet_interval="none" 
    frozen_headers="3" 
    embedded_titles="yes"
	)   
style=my_monospace; 
;
PROC TABULATE
DATA=WORK.have
S=[foreground=black just=c cellwidth=125];
	VAR N;
	CLASS Field1 /	ORDER=UNFORMATTED MISSING;
	CLASS Field2 / 	ORDER=UNFORMATTED MISSING;
	CLASS Name /	ORDER=UNFORMATTED MISSING;
	TABLE 
/*Row Dimension*/
Name *'N'=' '  
ALL*{style=[font_weight=bold just=center background = cxE5B82E ]}
,
/* Column Dimension */
Field1*Field2 =' ' 
ALL*{style=[font_weight=bold just=center background = cxE5B82E ]}	
;	
RUN;

The output it generates:

the output I'm trying to achieve:

I'm lost in the styles part - there's so many and I'm not sure what to change to get what I want. Any help with achieving the output I want and how to navigate and understand styles would be helpful.

Re: How to change the sub header styles in proc tabulate using a custom template?

ballardw — Wed, 03 May 2023 07:42:50 GMT

It helps to provide a narrative of what you want as well.

The CLASSLEV statement is where you set most overrides for style appearance of column or row header variables.

This gets pretty close to what you are requesting for appearance.

Note that you can address border combined with top, bottom, right or left with color and width (this is line width: borderbottomwidth=1mm for example to set 1mm width lines). I am not sure if we are getting an interaction with your custom style or not that wasn't coloring the border with the box area. So added a style override for the box area right border

PROC TABULATE 
DATA=WORK.have
S=[foreground=black just=c cellwidth=125];
	VAR N;
	CLASS Field1 /	ORDER=UNFORMATTED MISSING ;
	CLASS Field2 / 	ORDER=UNFORMATTED MISSING ;
	CLASS Name /	ORDER=UNFORMATTED MISSING;
   classlev field1 /style={borderbottomcolor=red borderrightcolor=red borderleftcolor=red};
   classlev field2 /style={bordertopcolor=red borderrightcolor=red borderleftcolor=red};
   TABLE 
      /*Row Dimension*/
      Name *'N'=' '  
      ALL*{style=[font_weight=bold just=center background = cxE5B82E ]}
      ,
      /* Column Dimension */
      Field1*Field2 =' ' 
      ALL*{style=[font_weight=bold just=center background = cxE5B82E ]}	
      /box={style=[borderrightcolor=red]}
   ;	
RUN;

You could change the text color and font as well with classlev. If you need more control based on the value of the cell you could use one or more formats that use the value of the cell as the start value and the value of the property as the "label" of the format and then use the style element referencing that format such as style=[textcolor=row1colorformat.]

Re: How to change the sub header styles in proc tabulate using a custom template?

SASAlex101 — Wed, 03 May 2023 14:04:07 GMT

Thanks! looks like the custom style is blocking the CLASSLEV style elements. So if I don't use the custom style, I'll have to code all the style elements manually in proc tabulate. This is a workaround I suppose. It would have been nice to have the custom style template work for me though.

Re: How to change the sub header styles in proc tabulate using a custom template?

SASAlex101 — Wed, 03 May 2023 16:54:57 GMT

I'm very close to the custom style I'm going for just can't hit that one cell with the style!~


PROC TABULATE
DATA=WORK.have

S=[foreground=black just=c cellwidth=125 
Font = ("Trebuchet MS",8pt)
foreground=black
BORDERCOLOR=white  
]
;
	VAR N /	style={
&standardcellformat.
};
keyword all / style=[&standardcellformat. vjust=center cellwidth=125];
keyword N / style=[&standardcellformat. just=center cellwidth=125];

	CLASS Field1 /	style={
borderbottomcolor=cxD3D3D3 
&standardcellformat.
} ORDER=UNFORMATTED MISSING ;

CLASS Field2 / 	style={
bordertopcolor=cxD3D3D3 
&standardcellformat.
} ORDER=UNFORMATTED MISSING ;

CLASS Name /style={
&standardcellformat.
}	ORDER=UNFORMATTED MISSING;

classlev Field1 /
style={
borderbottomcolor=red 
borderrightcolor=red 
borderleftcolor=red
&standardcellformat.
};
classlev Field2 /
style={
borderrightcolor=red 
borderleftcolor=red
&standardcellformat.
};

classlev Name /
style={
bordercolor=lightgrey
&standardcellformat.
};

TABLE 
/*Row Dimension*/
Name*'N' = ' ' 

ALL*{style=[
font_weight=bold 
just=center 
background = cxE5B82E 
foreground=black 
BORDERCOLOR=cxE5B82E  
Font=("Trebuchet MS",9pt)]}
,

/* Column Dimension */
Field1 * Field2 =' ' 
ALL*{style=[
font_weight=bold 
just=center 
background = cxE5B82E 
foreground=black 
BORDERCOLOR=cxE5B82E  
Font=("Trebuchet MS",9pt)]
}
	
/ box={style=[background = lightgrey borderrightcolor=red]};

;	
RUN;

Re: How to change the sub header styles in proc tabulate using a custom template?

Kathryn_SAS — Thu, 04 May 2023 12:56:46 GMT

That is coming from the part of the Table statement I have commented out below. I do not think you need that.

TABLE 
/*Row Dimension*/
Name/**'N' = ' ' */

ALL*{style=[
font_weight=bold 
just=center 
background = cxE5B82E 
foreground=black 
BORDERCOLOR=cxE5B82E  
Font=("Trebuchet MS",9pt)]}
,