Excel Worksheet Name Overwriting

Reply
Contributor
Posts: 31

Excel Worksheet Name Overwriting

I'm very new to SAS so I'm going to assume this is an easy fix that I just can't figure out!  I have created an excel file with multiple worksheets.  My problem is my first worksheet is labeled "Page 1" for example, Second, "Page 2", and third is "Page 3".  What's happening is they are getting labeled "Page 2", "Page 2 2", and "Page 3".  The other thing that is happening is my freeze pane for my columns is looking at what I want for Page 2 and not for Page 1.  The data on Page 1 is correct, it's just the tab label and the freeze pane.  A snippet of my code is as follows;

ods_all_ close;

ods listing close;

ods tagsets.ExcelXP path = 'location of my file'

file = 'NameOfFile.xls' style=MINIMAL;

ods tagsets.ExcelXP options(sheet_name='Page 1'

     frozen_headers="1"

     frozen_rowheaders="2")


PROC REPORT DATA=WORK.TblPage1

.....  Code to create my first worksheet ....

ods tagsets.ExcelXP options(sheet_name='Page 2'

     frozen_rowheaders="8");

PROC PRINT DATA=WORK.TblPage2 noobs

RUN;quit;

ods tagsets.ExcelXP options(sheet_name='Page 3'

     frozen_rowheaders="7");

PROC PRINT DATA=WORK.TblPage3 noobs

RUN;quit;

ods tagsets.ExcelXP close;

Super User
Super User
Posts: 7,716

Re: Excel Worksheet Name Overwriting

I can't see from that what would be the issue.  The following code runs correctly on my 9.3:

ods _all_ close;

ods tagsets.excelxp file="s:\temp\rob\temp.xml" options(sheet_name="Page 1" frozen_headers="1" frozen_rowheaders="2");

proc report data=sashelp.cars nowd;

  columns _all_;

run;

ods tagsets.excelxp options(sheet_name="Page 2" frozen_rowheaders="8");

proc print data=sashelp.cars noobs;

run;

ods _all_ close;

Are you sure there is nothing else your doing, maybe setting sheet_interval option or something in your code?

Contributor
Posts: 31

Re: Excel Worksheet Name Overwriting

No, my proc report is doing some cumputes to alternate the row colors (white/gray), Then I do another set of computes to color any cell that has a negative number, that's it. 

Super User
Posts: 19,105

Re: Excel Worksheet Name Overwriting

What version of tagsets are you using? After you submit your code you'll see the version and date. Make sure it's the latest, 1.113 I think.

Contributor
Posts: 31

Re: Excel Worksheet Name Overwriting

Here's what I get...

NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.130, 08/02/2013).

Ask a Question
Discussion stats
  • 4 replies
  • 265 views
  • 0 likes
  • 3 in conversation