The SAS Output Delivery System and reporting techniques

ExcelXP, Tagattr and mergeAcross

Reply
Super User
Posts: 19,770

ExcelXP, Tagattr and mergeAcross

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;

Valued Guide
Posts: 2,177

ExcelXP, Tagattr and mergeAcross

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 ?    

Super User
Posts: 19,770

ExcelXP, Tagattr and mergeAcross

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. 

Valued Guide
Posts: 2,177

ExcelXP, Tagattr and mergeAcross

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

Super User
Posts: 19,770

ExcelXP, Tagattr and mergeAcross

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

Valued Guide
Posts: 2,177

ExcelXP, Tagattr and mergeAcross

: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

Super User
Posts: 19,770

ExcelXP, Tagattr and mergeAcross

Yeah, same thing in my log.

Opening a ticket with Tech Support.

Thanks!

New Contributor
Posts: 2

Re: ExcelXP, Tagattr and mergeAcross

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

SAS Super FREQ
Posts: 8,864

Re: ExcelXP, Tagattr and mergeAcross

Posted in reply to JonWilliamson


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
N/A
Posts: 1

Re: ExcelXP, Tagattr and mergeAcross

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

Ron.

Super User
Posts: 19,770

Re: ExcelXP, Tagattr and mergeAcross

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

Ask a Question
Discussion stats
  • 10 replies
  • 2601 views
  • 6 likes
  • 5 in conversation