align column head to Top via tagsets.excelXP

[deleted_user]

is it simple?
for a worksheet generated via tagsets.excelXP
... to modify the column headers handling (PROC TEMPLATE ?) to "Top-align", so that they don't overlap the autofilter widget.

I have little confidence as I approach this ... including that I'll be able to find the relevant documentation of the features of PROC TEMPLATE 😉

Your support is appreciated.. greatly

PeterC

[Cynthia_sas]

Hi:
There is a style attribute called VJUST and in regular ODS HTML, RTF, PDF, etc, you'd use that. But sometimes Excel doesn't like the SAS style alignment. Perhaps one of the ODS developers knows whether Excel respects VJUST.

cynthia

[deleted_user]

Thank you Cynthia....

I'm missing something. [pre]ods _all_ close ;
ods tagsets.excelxp file= odsout( url= none) rs= none ;
ods tagsets.excelxp options( height= '20'
absolute_column_width= '10' ) ;
proc print LABEL data= sashelp.class style(header)={vjust=Top} ;
LABEL AGE = 'LETS TRY to wrap A WIDE LABEL FOR AGE' ;
run;
ods _all_ close ;

NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.70, 06/05/07)[/pre] The fileref ODSOUT is effective at delivering the generated xml, into excel.

The control of cell heights and column widths work just fine for me.

I was hoping to pin down control of justification within the PROC PRINT syntax.
Oddly, on the workbook generated, the only cells that do not have the "wrap" attribute, are the column headers (practicaly the only cells I would want to wrap!)

Does this mean I have to go into PROC TEMPLATE ?


PeterC

[deleted_user]

so, it was time to delve into PROC TEMPLATE (see later).
That allowed my proc print syntax to become a little simpler :[pre]proc print LABEL data= sashelp.class style(header)= Header ;[/pre]

The definition of the "style(header)= Header" header was made in :[pre]PROC TEMPLATE;
DEFINE style styles.myXstyle ;
parent = styles.default;
style Header from Header /
just = left
vjust = top;
END;
run;[/pre] and that style with the special Header is invoked with [pre]ods tagsets.excelxp file= odsout( url= none) rs= none style=myXstyle ; [/pre]

It will do for now, but does seem a little convoluted, especially when style requests like justification are able to be defined within the proc print syntax.

PeterC

[Cynthia_sas]

Peter:
In my limited understanding of ExcelXP, I think the issue has to do with how Microsoft respects or does not respect style information. The SAS style template goes into building the in-line style section for the ExcelXP XML file. Then the style overrides that come into the picture, via the procedure syntax are put "down" in the tag for the cell or element itself.

I think that Microsoft, by design, mostly respects what comes in via the in-line style section. I do find that backgrounds and foreground colors, fonts, formats and cell types are generally respected when they come into the XML via a style override (procedure syntax) -- but very frequently alignment seems to be ignored.

Although it may seem like the PROC TEMPLATE method is convoluted, do remember that if your PROC PRINT was followed by a PROC GLM or a PROC REG or a PROC MEANS, that their headers would ALSO be affected by the STYLE template and since they do not have any internal syntax to change their headers, this is a good thing.

cynthia