vertical alignment of angular header using ODS tagsets

[Chandra_t]

Hi All,

We have a report with column header labels a little longer than the usual (we have column descriptions in multiple languages), so we are using angular headers to accommodate the lengthy labels. (using tagattr="rotate=75") option. we also want to align the text to center both vertically and horizontally using just and vjust options, but we are not able to get the desired output. Seems the tagattr option is overriding the other style statements. Please find the sample code snippet and report snapshot (actual and required output) . Is there any other way round to get the desired effect.

Thanks in advance.

Regards,

Chandra

Sample Code:

ods path work.templat(update) sashelp.tmplmst(read);

proc template;
define style styles.myreportExcel;
parent = styles.sansPrinter ;
style fonts from fonts /
        'TitleFont2' = ("Arial",14pt,Bold Italic)               
        'TitleFont' = ("Arial",11pt,Bold)           
        'StrongFont' = ("Arial",12pt,Bold)                       
        'EmphasisFont' = ("Arial",12pt,Italic)                   
        'FixedEmphasisFont' = ("Courier",11pt,Italic)                       
        'FixedStrongFont' = ("Courier",11pt,Bold)                           
        'FixedHeadingFont' = ("Courier",11pt,Bold)                           
        'BatchFixedFont' = ("Courier",9pt)                   
        'FixedFont' = ("Courier",11pt)                                       
        'headingEmphasisFont' = ("Arial",9pt,Bold Italic)       
        'headingFont' = ("Arial",9pt,Bold)                     
        'docFont' = ("Arial",10pt)  ;
  style angle_header from header /
  font_size = 9pt
  just = center
  vjust = center
  tagattr = 'rotate:75'
    ;

end;
run;
quit;

filename dash_rpt "&rpt_output./class_report..xls";
options center missing='';
title;
ods html close;
ods listing close;

ods tagsets.excelxp file=dash_rpt
style=styles.myreportExcel
options( Sheet_Name="class"
row_heights="40,15"
row_height_fudge = "15"
default_column_width="7.5, 5,7.5, 5.5"
width_fudge='0.8' Orientation='Portrait' Embedded_Titles='Yes' skip_space='1,1,0,0,1' );

TITLE1 J=LEFT H=12PT BOLD "Student Report";
TITLE2 J=L  H=12PT BOLD  " Week 08 (2012-02-22 to 2012-02-28)";

PROC REPORT DATA= sashelp.class NOWD MISSING CONTENTS='' SPLIT="*"
STYLE(HEADER)={BACKGROUND=white FOREGROUND=black font_size = 9pt vjust=center JUST=CENTER};

COLUMN  Name Sex Age Height Weight;
 
DEFINE Name/ STYLE(COLUMN)=[JUST=Left] style(header)=angle_header;
DEFINE sex / STYLE(COLUMN)=[JUST=C] style(header)=angle_header;
DEFINE age / DISPLAY style(header)=angle_header;
DEFINE height / style={tagattr="format:###,###,###"} style(header)=angle_header DISPLAY;
DEFINE weight/ style={tagattr="format:###,###,###"} style(header)=angle_header  DISPLAY;


COMPUTE height;
      IF height gt 60 THEN DO;
        CALL define("height",'style','style={background=lightgrey font_weight=bold }') ;
   
    END;
 
  ENDCOMP;

  COMPUTE weight ;
      IF weight gt 100  THEN DO;
        CALL define("weight",'style','style={background=lightgrey font_weight=bold }') ;
    END;
  ENDCOMP;

label name="Student Name"
    sex ="Gender"
    Age ="Age of /* the student"
    height = "Height of /* the student"
    weight= "Weight of  /* the student"
    ;
    run;

title1;
title2;

ods tagsets.Excelxp close;

ods html close;
ods listing;

PROC TEMPLATE;
    DELETE styles.myreportExcel;
RUN;

[Cynthia_sas]

Hi:

  It is not VJUST=CENTER. The correct position is MIDDLE or M. See the doc:

  Hope this helps,

cynthia

[Chandra_t]

Hi Cynthia,

Thanks for your mail. I have used VJUST=MIDDLE option, but  I am still not getting my header aligned to the center. pls find the updated code.

%let rpt_output=%nrquote(/data/reports);

ods path work.templat(update) sashelp.tmplmst(read);

proc template;
define style styles.myreportExcel;
parent = styles.sansPrinter ;
style fonts from fonts /
        'TitleFont2' = ("Arial",14pt,Bold Italic)               
        'TitleFont' = ("Arial",11pt,Bold)           
        'StrongFont' = ("Arial",12pt,Bold)                       
        'EmphasisFont' = ("Arial",12pt,Italic)                   
        'FixedEmphasisFont' = ("Courier",11pt,Italic)                       
        'FixedStrongFont' = ("Courier",11pt,Bold)                           
        'FixedHeadingFont' = ("Courier",11pt,Bold)                           
        'BatchFixedFont' = ("Courier",9pt)                   
        'FixedFont' = ("Courier",11pt)                                       
        'headingEmphasisFont' = ("Arial",9pt,Bold Italic)       
        'headingFont' = ("Arial",9pt,Bold)                     
        'docFont' = ("Arial",10pt)  ;
  style angle_header from header /
  font_size = 9pt
  just = center
  vjust = MIDDLE
  tagattr = 'rotate:75'
    ;

end;
run;
quit;

filename dash_rpt "&rpt_output./class_report..xls";
options center missing='';
title;
ods html close;
ods listing close;

ods tagsets.excelxp file=dash_rpt
style=styles.myreportExcel
options( Sheet_Name="class"
row_heights="40,15"
row_height_fudge = "15"
default_column_width="7.5, 5,7.5, 5.5"
width_fudge='0.8' Orientation='Portrait' Embedded_Titles='Yes' skip_space='1,1,0,0,1' );

TITLE1 J=LEFT H=12PT BOLD "Student Report";
TITLE2 J=L  H=12PT BOLD  " Week 08 (2012-02-22 to 2012-02-28)";

PROC REPORT DATA= sashelp.class NOWD MISSING CONTENTS='' SPLIT="*"
STYLE(HEADER)={BACKGROUND=white FOREGROUND=black font_size = 9pt vjust=M JUST=CENTER};

COLUMN  Name Sex Age Height Weight;
 
DEFINE Name/ STYLE(COLUMN)=[JUST=Left] style(header)=angle_header;
DEFINE sex / STYLE(COLUMN)=[JUST=C] style(header)=angle_header;
DEFINE age / DISPLAY style(header)=angle_header;
DEFINE height / style={tagattr="format:###,###,###"} style(header)=angle_header DISPLAY;
DEFINE weight/ style={tagattr="format:###,###,###"} style(header)=angle_header  DISPLAY;

COMPUTE height;
      IF height gt 60 THEN DO;
        CALL define("height",'style','style={background=lightgrey font_weight=bold }') ;
   
    END;
 
  ENDCOMP;

  COMPUTE weight ;
      IF weight gt 100  THEN DO;
        CALL define("weight",'style','style={background=lightgrey font_weight=bold }') ;
    END;
  ENDCOMP;

label name="Student Name"
    sex ="Gender"
    Age ="Age of /* the student"
    height = "Height of /* the student"
    weight= "Weight of  /* the student"
    ;
    run;

title1;
title2;

ods tagsets.Excelxp close;

ods html close;
ods listing;

PROC TEMPLATE;
    DELETE styles.myreportExcel;
RUN;

[Cynthia_sas]

Hi:

  Then it's probably time to work with Tech Support. When I change vjust=middle in the code you posted (2 places -- once in the Template code and once in the PROC REPORT statement) I get this output.

So it has to be something else. I ran this in SAS 9.4 and opened the file with Excel 2013.

cynthia