The SAS Output Delivery System and reporting techniques

Forcing formats on excel in ods excelxp

Reply
Occasional Contributor
Posts: 18

Forcing formats on excel in ods excelxp

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;

SAS Super FREQ
Posts: 8,743

Re: Forcing formats on excel in ods excelxp

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
Occasional Contributor
Posts: 18

Re: Forcing formats on excel in ods excelxp

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.

SAS Super FREQ
Posts: 8,743

Re: Forcing formats on excel in ods excelxp

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
Ask a Question
Discussion stats
  • 3 replies
  • 388 views
  • 0 likes
  • 2 in conversation