BookmarkSubscribeRSS Feed
Reeza
Super User

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;

11 REPLIES 11
Peter_C
Rhodochrosite | Level 12

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 ?    

Reeza
Super User

Unfortunately I wouldn't know what to look for in the XML file Smiley Sad

I'll tried creating a sample in Excel and looking for similarities in the file but nothing jumps out at me. 

Peter_C
Rhodochrosite | Level 12

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

Reeza
Super User

Thanks Peter, but that doesn't work either Smiley Sad

Peter_C
Rhodochrosite | Level 12

: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

Reeza
Super User

Yeah, same thing in my log.

Opening a ticket with Tech Support.

Thanks!

JonWilliamson
Calcite | Level 5

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

Cynthia_sas
SAS Super FREQ


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;


header_spanning_report.png
manleyr
Calcite | Level 5

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

Ron.

Reeza
Super User

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

JonathanWill
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 4919 views
  • 6 likes
  • 6 in conversation