topic Re: ExcelXP, Tagattr and mergeAcross in ODS and Base Reporting

ExcelXP, Tagattr and mergeAcross

Reeza — Wed, 09 Nov 2011 21:55:03 GMT

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;

ExcelXP, Tagattr and mergeAcross

Peter_C — Wed, 09 Nov 2011 22:43:38 GMT

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 ?

ExcelXP, Tagattr and mergeAcross

Peter_C — Wed, 09 Nov 2011 22:55:50 GMT

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"};

ExcelXP, Tagattr and mergeAcross

Reeza — Wed, 09 Nov 2011 23:17:52 GMT

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.

ExcelXP, Tagattr and mergeAcross

Reeza — Wed, 09 Nov 2011 23:18:37 GMT

Thanks Peter, but that doesn't work either

ExcelXP, Tagattr and mergeAcross

Peter_C — Wed, 09 Nov 2011 23:52:45 GMT

: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

ExcelXP, Tagattr and mergeAcross

Reeza — Thu, 10 Nov 2011 00:53:18 GMT

Yeah, same thing in my log.

Opening a ticket with Tech Support.

Thanks!

Re: ExcelXP, Tagattr and mergeAcross

JonWilliamson — Fri, 06 Jul 2012 10:28:12 GMT

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

if merge across is set on col 1 then col 2 should be omitted to give:

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

Re: ExcelXP, Tagattr and mergeAcross

Cynthia_sas — Fri, 06 Jul 2012 14:40:04 GMT

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;

Re: ExcelXP, Tagattr and mergeAcross

manleyr — Thu, 11 Oct 2012 13:13:21 GMT

Hi Did you ever get an answer from tech support? As I would like this functionality.

Ron.

Re: ExcelXP, Tagattr and mergeAcross

Reeza — Thu, 11 Oct 2012 19:30:03 GMT

Yes, basically it doesn't work as expected so don't use it.

Re: ExcelXP, Tagattr and mergeAcross

JonathanWill — Mon, 04 Dec 2017 15:08:23 GMT

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