topic Re: ODS EXCEL TopAlign data cells in ODS and Base Reporting

ODS EXCEL TopAlign data cells

RichardDeVen — Wed, 06 Oct 2021 18:05:43 GMT

Is there a way to top align data cells in ODS EXCEL using a custom style with a STYLE or CLASS override?

In this example I can set the font to be 10pt Arial, don't know how to top align.

dm 'clear log' log;

proc template;
  source styles.default;
  source styles.statistical;
  define style special;
  parent = styles.statistical;
  style fonts from fonts /
    'HeadingFont'= ('Arial', 10pt, bold)
    'DocFont'    = ('Arial', 10pt)
  ;
  end;
run;

ods excel 
  file="special%sysfunc(monotonic()).xlsx" 
  style=special 
  options(
    zoom='85'
    row_heights='15,25'
  )
;

proc print noobs label data=sashelp.class;
run;

ods excel close;

Re: ODS EXCEL TopAlign data cells

Cynthia_sas — Wed, 06 Oct 2021 18:26:23 GMT

Hi:

These worked for me without changing any suboptions for ODS Excel.

1) Using Style overrides:

2) Using PROC Template:

(I only changed the header cells. If you want to top align the data cells too, you'd need a style override for style(column) in PROC REPORT and for style(data) in PROC PRINT. Inside the style template, you'd use CLASS data in addition to CLASS header.)

Cynthia

Re: ODS EXCEL TopAlign data cells

ballardw — Wed, 06 Oct 2021 19:56:00 GMT

The default cell sizes may not allow for changes in justification so you may need to make the cells taller first.

This changes the column headings as well as the body of the table using Proc Print. Proc Print will make all the cells the same height, largest if you specify multiple different heights.

proc print data=sashelp.class;
  var name / style=[vjust=top height=.5in];
  var sex  / style=[vjust=center ];
  var age height weight / style=[vjust=bottom ];
run;

Re: ODS EXCEL TopAlign data cells

RichardDeVen — Wed, 06 Oct 2021 23:33:04 GMT

Thanks for the replies.

Turns out the template statement needed is:

  style data from data /
    VerticalAlign = Top
  ;
  end;

I had tried name value pair in quotes. "VerticalAlign"="Top"

Re: ODS EXCEL TopAlign data cells

Cynthia_sas — Thu, 07 Oct 2021 00:02:33 GMT

Hi:
VJUST=T will also work. or VERTICALALIGN=T. Typically, the only style attributes that need to be quoted are text strings (like for Table of Contents) or font names which contain spaces, or a string of font family specifications.
Cynthia