I'm trying to use the tagattr mergeAcross style on a field that isn't the last field.
It works if its the last field, but I want it to be the middle field...is there a way to get this to work? I keep getting an XML error in the file, but I could be missing something.
If I print just model and comment ou the make and type columns it works fine.
Ideally, I'd like to print the first table with a filed that merged across and the second table below without the field merged across, to fit things on the table and have it look nice as well.
Thanks!
ods listing close;
ods tagsets.excelxp file="C:\temp\example6.xls" style=journal options(sheet_interval='none' embedded_titles='yes');
title ;
proc report data=sashelp.cars (obs=100) nowd;
columns model make type;
define model / 'Name' style={tagattr="mergeAcross:yes"};
define make/display;
define type/display;
run;
/*proc freq data=sashelp.class;*/
/* tables age;*/
/*run;*/
ods tagsets.excelxp close;
ods listing;
have you looked (in notepad) at how much difference there is between the required result saved in excel as xml format and the output from your example6 ?
Unfortunately I wouldn't know what to look for in the XML file
I'll tried creating a sample in Excel and looking for similarities in the file but nothing jumps out at me.
lifted from the options( doc='help')
<mergeacross: yes/No/number>
MergeAcross is to force a cell to merge across the current width of the worksheet.
Using a number will cause the cell to merge across that many columns.
try
define model / 'Name' style={tagattr="mergeAcross:2"};
Thanks Peter, but that doesn't work either
:smileyinfo:
running in SAS9.3 dosen't solve it.
The error log from excel shows many repetitions of the text
XML ERROR in Table
REASON: Bad Value
FILE: C:\temp\example6.xls
GROUP: Row
TAG: Cell
ATTRIB: Index
VALUE: 2
XML ERROR in Table
REASON: Bad Value
FILE: C:\temp\example6.xls
GROUP: Row
TAG: Cell
ATTRIB: Index
VALUE: 3
Yeah, same thing in my log.
Opening a ticket with Tech Support.
Thanks!
Did you get anywhere with Tech Support?
I can see the problem in the XML:
As the cells are written into the XML they are assigned sequential index numbers, but when you use the mergeacross option Excel expects the cells that are being merge to be ommitted from the sequence.
i.e. in a 3 column table the cells are defined as
<cell ss:Index="1"></cell>
<cell ss:Index="2"></cell>
<cell ss:Index="3"></cell>
if merge across is set on col 1 then col 2 should be omitted to give:
<cell ss:Index="1" ss:MergeAcross="1"></cell>
<cell ss:Index="3"></cell>
However in the XML created by SAS the cell with index 2 is still there.
What we need is some way of getting the tagset to not output columns that are 'underneath' the merged across cell. I've tried to fool sas with columns defined with noprint in proc report, but it kindly shuffles everything along.
Jon
Hi:
See the attached screenshot. You can make the string "Name" span all 3 columns in the report by using regular spanning in the COLUMN statement. Screenshot produced with code below/code run in SAS 9.3, output opened with Excel 2010.
cynthia
ods listing close;
ods tagsets.excelxp file="C:\temp\example6.xml"
style=journal options(sheet_interval='none' embedded_titles='yes');
title ;
proc report data=sashelp.cars (obs=100) nowd;
columns ("Name" model make type);
define model / 'Model' ;
define make/display;
define type/display;
run;
ods tagsets.excelxp close;
ods listing;
Hi Did you ever get an answer from tech support? As I would like this functionality.
Ron.
Yes, basically it doesn't work as expected so don't use it.
I've run into this issue once more and this time I had a little more time to understand the tagset code. As I put in my post all that time ago the issue is with the ss:Index values when merging cells.
Using a trivial example :
proc report data=sashelp.cars;
columns make model;
define make / style={tagattr="mergeacross:1"};
run;
Generates XML like this:
<Row ss:AutoFitHeight="1" ss:Height="18"> <Cell ss:StyleID="header__c" ss:MergeAcross="1" ss:Index="1"> <Data ss:Type="String">Make</Data> </Cell> <Cell ss:StyleID="header__c" ss:Index="2"> <Data ss:Type="String">Model</Data> </Cell> </Row>
Which Excel 2010 refuses to open claiming it's a corrupt file. (I'm sure earlier Excel was more helpful and indicated which element in the XML it had an issue with).
I've created a modified version of the tagset which correctly counts the columns when using mergeacross and outputs appropriate ss:Index values. So the same code generates XML like this:
<Row ss:AutoFitHeight="1" ss:Height="18"> <Cell ss:StyleID="header__c" ss:MergeAcross="1" ss:Index="1"> <Data ss:Type="String">Make</Data> </Cell> <Cell ss:StyleID="header__c" ss:Index="3"> <Data ss:Type="String">Model</Data> </Cell> </Row>
Which loads into Excel 2010 correctly.
The amendments made to the SAS issued version 1.131 are:
In event Row start, initialise a counter for merged columns (and unset in Row end)
In event mergeAcross, maintain the count of merged columns
In event cellStart, calculate a new value for ss:Index using the additional information.
I think there may be more needed in mergeAcross to make this fix applicable for all uses.
The full modified tagset is attached. I've called this v1.131.1 and referenced this forum message thread in the changes.
Would be great if someone in SAS could take a look and use this as a base to issue a fix.
Jonathan
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.