The SAS Output Delivery System and reporting techniques

Tagset.Excelxp rendering Table header

Reply
Occasional Contributor
Posts: 10

Tagset.Excelxp rendering Table header

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.
SAS Super FREQ
Posts: 8,864

Re: Tagset.Excelxp rendering Table header

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

Re: Tagset.Excelxp rendering Table header

Posted in reply to Cynthia_sas
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
Ask a Question
Discussion stats
  • 2 replies
  • 310 views
  • 0 likes
  • 2 in conversation