topic Forcing formats on excel in ods excelxp in ODS and Base Reporting

Forcing formats on excel in ods excelxp

christa — Fri, 20 Nov 2015 08:00:30 GMT

Hi there,

I am trying to create a "wellformed" report using ods excelxp.

there are two problems left:

while vjust=m works fine in the box statement, i cant find a way to to the same for the title "cars".

I cant get rowheights to work for me.

Any suggestions? Code is below.

Thank you in advance

proc template;
define style styles.test; parent=styles.minimal;
STYLE SystemTitle1 / bordertopwidth=0 borderleftwidth=0 borderrightwidth=0 FONT_FACE = "Arial" FONT_SIZE = 10pt FONT_WEIGHT = bold FONT_STYLE = roman FOREGROUND = black BACKGROUND = white bordertopwidth=0 borderbottomwidth=0 borderleftwidth=0 borderrightwidth=0 ;
STYLE systemfootnote2 / FONT_FACE = "Arial" FONT_SIZE = 7pt FONT_WEIGHT = light FONT_STYLE = roman FOREGROUND = black BACKGROUND = white;
style body from body / background=white;
style table from table / borderwidth=0 cellspacing=0 background=white;
style header from header / font_weight=light font_size=8pt borderleftwidth=1 borderrightwidth=1 bordertopwidth=1 borderbottomwidth=1 vjust=center;
style topc from header / font_weight=bold font_size=8pt borderleftwidth=1 borderrightwidth=1 bordertopwidth=1 borderbottomwidth=1 vjust=top just=left;
style rowheader from rowheader /font_weight=light font_size=8pt bordertopwidth=0 borderbottomwidth=0 borderleftwidth=0 borderrightwidth=0 ;
style data from data / bordertopwidth=0 borderbottomwidth=0 borderleftwidth=0 borderrightwidth=0 bordercolor=white cellwidth=80
tagattr="format:# ### ##0; - # ### ##0; —; @ ";
end;

ods listing close;
Options papersize=A4 TopMargin = 0.984251969in LeftMargin = 0.1in RightMargin = 0.1in;
run;

Ods Tagsets.ExcelXP style=styles.test
file= '\\c04-fs1.hestala.de\Abt_1\projekte\hedas\HEPAS\cars.xml'

options(DPI='600' sheet_interval='none' sheet_name = "Hessen"
                           merge_titles_footnotes='yes' autofit_height='no' SKIP_SPACE='0,0,0,0,0,0,0' Row_Heights= '40,20,10,0,0,0,0'
                           Center_Vertical= 'no' Center_Horizontal= 'yes' gridlines='no'
                           embedded_titles='yes' embedded_footnotes='yes' sheet_interval='none');

TITLE vjust=top "Cars";

ods tagsets.excelxp options(sheet_interval='none' sheet_name = "cars");
data test; set sashelp.cars; run;

proc tabulate data=sashelp.cars;
var length;
class make model/MLF missing;
classlev make/style={cellwidth=101};
classlev model/style={cellwidth=136 just=r};
table make='',
model=''*length= ""* sum=''
/ printmiss misstext='0' BOX={LABEL='make' style={just=c vjust=m}};
run;

Re: Forcing formats on excel in ods excelxp

Cynthia_sas — Sat, 21 Nov 2015 01:07:05 GMT

Hi:
I would expect you to get this error with the TITLE statement. TITLE only supports J=. So VJUST should give you an error like this:
WARNING: The TITLE statement is ambiguous due to invalid options or unquoted text.

But if you want VJUST for a title (which I don't think you'd need), you'd have to change the style template for the SystemTitle element.

cynthia

Re: Forcing formats on excel in ods excelxp

christa — Mon, 23 Nov 2015 08:14:25 GMT

Hi Cynthia,

thank you for your suggestion. I tried

proc template;

define style styles.test; parent=styles.minimal;

STYLE SystemTitle / vjust=middle bordertopwidth=0 borderleftwidth=0 borderrightwidth=0 FONT_FACE = "Arial" FONT_SIZE = 10pt FONT_WEIGHT = bold FONT_STYLE = roman FOREGROUND = black BACKGROUND = white bordertopwidth=0 borderbottomwidth=0 borderleftwidth=0 borderrightwidth=0 ;

STYLE SystemTitle1 / vjust=middle bordertopwidth=0 borderleftwidth=0 borderrightwidth=0 FONT_FACE = "Arial" FONT_SIZE = 10pt FONT_WEIGHT = bold FONT_STYLE = roman FOREGROUND = black BACKGROUND = white bordertopwidth=0 borderbottomwidth=0 borderleftwidth=0 borderrightwidth=0 ;

But it did not influence the appearance in excel ...

by the way, I had no error messages for the title statement.

Re: Forcing formats on excel in ods excelxp

Cynthia_sas — Wed, 25 Nov 2015 14:50:42 GMT

I got an error message in the log in SAS 9.4 when I tried VJUST in the TITLE statement. It could be that there are some style elements in your template that are not respected by Excel when it opens the XML file created by TAGSETS.EXCELXP. This is more of a question for Tech Support.
cynthia