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.
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.]
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.
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;
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)]}
,
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: