Tagset.Excelxp rendering Table header

Domenico — Mon, 09 May 2011 12:16:59 GMT

Hi i'm trying to render a table header for an excel output.
The problem is that i can't merge cells as i prefer.
I tried the tagattr mergeDown and mergeAcross, but i guess i used it in a wrong way.

Anyway below is the code of the proc template to override the style "sansPrinter" for the table, header, data ...
I manage the output with a proc report so i have more options to render the header, but maybe there is another way using the proc print.

Basically, i want the cell with the content "Wohnsitzgemeinde" to mergeDown 2 cells and the cell "Berufsauspendler" to mergeAcross 6 cells.
The cells with contents "zusammen" and "darunter weiblich" should mergeDown 1 cell each and the cell "im Alter von ... bis unter ... Jahren" to mergeAcross 4 cells.
Sorry for german content in cells, but actually the content is not important.
This is what i'm trying to get, when i look in the xml code.

Wohnsitzgemeinde
ss:Name="Print_Titles"/>
Berufsauspendler
ss:Name="Print_Titles"/>

zusammen
ss:Name="Print_Titles"/>
darunter weiblich
ss:Name="Print_Titles"/>
im Alter von ... bis unter ... Jahren
ss:Name="Print_Titles"/>

unter 25
ss:Name="Print_Titles"/>
25 - 35
ss:Name="Print_Titles"/>
35 - 45
ss:Name="Print_Titles"/>
45 - 65
ss:Name="Print_Titles"/>
65 und mehr
ss:Name="Print_Titles"/>

And this is what i get :

Berufsauspendler

im Alter von ... bis unter ... Jahren

Wohnsitzgemeinde
zusammen
darunter weiblich
unter 25
25 - 35
35 - 45
45 - 65
65 und mehr

How can i use the tagattr mergeDown and mergeAcross to receive the first table header?

Here my code:

proc template;
define style styles.XLsansPrinter;
parent=styles.sansPrinter;

style table from output /
color=black
background=white
borderrightwidth=0.75pt
borderleftwidth=0.75pt
bordertopwidth=0.75pt
borderbottomwidth=0.75pt
cellspacing=0pt
cellpadding=3pt;

style body from body /
margintop=.5in
marginbottom=.5in
marginleft=.25in
marginright=.25in;

style cell from container /
borderrightwidth=0pt
borderleftwidth=0pt
bordertopwidth=0pt
borderbottomwidth=0pt;

style header from header /
backgroundcolor=white
fontsize=10pt
textalign=center
verticalalign=middle;

style header_tab2 from header /
tagattr='mergeDown:2';

style data_all from data /
background=white
font_size=10pt
just=right;

style data_ags from data_all /
just=center
tagattr='format:Text';

end;
run; quit;

ods listing close;
ods tagsets.excelxp file="&g_ProjektVerzeichnis\ausgabe\tab2a.xml"
style=XLsansPrinter
options(embedded_titles='yes'
suppress_bylines='yes'
sheet_interval='none'
sheet_name="Tab2a"
center_horizontal='yes'
center_vertical='yes'
autofit_height='yes'
row_repeat='1-5'
absolut_column_width='10, 10, 10, 10, 12');

ods escapechar='^';

title1 bold justify=left 'Tab.: 2a. Berufsauspendler nach Geschlecht und Altersgruppen';

proc report data = work.tabelle2a nowindows spanrows split='^';
where (substr(Wohnort,1,2)='05');

columns Wohnort
("Berufsauspendler" (insgesamt weiblich
("im Alter von ... bis unter ... Jahren" u25 u35 u45 u65 ue65)));

define Wohnort / order "Wohnsitzgemeinde" style(header)=header_tab2
style(column)=data_ags;
define insgesamt / analysis "zusammen" style(header)=header
style(column)=data_all;
define weiblich / analysis "darunter^weiblich" style(header)=header
style(column)=data_all;
define u25 / analysis "unter^25" style(header)=header
style(column)=data_all;
define u35 / analysis "25 - 35" style(header)=header
style(column)=data_all;
define u45 / analysis "35 - 45" style(header)=header
style(column)=data_all;
define u65 / analysis "45 - 65" style(header)=header
style(column)=data_all;
define ue65 / analysis "65^und mehr" style(header)=header
style(column)=data_all;

run;
quit;

ods tagsets.excelxp close;
ods listing;

I hope you could understand my problem.

Re: Tagset.Excelxp rendering Table header

Cynthia_sas — Mon, 09 May 2011 16:50:35 GMT

Hi:
I have only ever used TAGATTR for FORMATS and FORMULAS. I have never used TAGATTR to perform a MERGEDOWN and I am not sure that what you want to do is possible. The ss:Mergedown is a style property instruction that Microsoft Excel knows how to interpret and render. I understand what you're trying to do in the style template, but what you are attempting may not work.

I'd recommend opening a track with Tech Support, they can tell you whether what you want to do is actually possible a style template method.

cynthia

Re: Tagset.Excelxp rendering Table header

Domenico — Tue, 10 May 2011 05:23:04 GMT

Hi Cynthia,

I was actually afraid to get this kind of answer. For the past 3 days i googled for the TAGATTR MERGEDOWN, but couln't find any papers, where it was explained how to use it.
Since i have to render quite a lot of table header for excel output and i often fail to merge cells, i will follow your suggestion and i will contact the Tech Support.
Thank you very much for your effort and for your quick reply

Domenico

topic Tagset.Excelxp rendering Table header in ODS and Base Reporting

Tagset.Excelxp rendering Table header

Re: Tagset.Excelxp rendering Table header

Re: Tagset.Excelxp rendering Table header