SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

ODS Tagsets ExcelXP Creating Multiple Workbooks

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

ODS Tagsets ExcelXP Creating Multiple Workbooks

I'm trying to use the ExcelXP tageset to create a single Excel workbook with multiple worksheets.  However every proc report it comes to it creates a whole new workbook with 1 added to the end of the name I specify.  For example I have the date in my workbook so it's Data 07-13-2015.xls.  SAS then creates a workbook for each section the first one being Data 07-13-2015.xls then the next one being Data 07-13-2016.xls and so on for each section.

I've tried adding a alpha character to the end such as Data 07-13-2015a.xls but that just results in Data 07-13-2015a.xls, Data 07-13-2015a1.xls, and so on.

Any ideas what could be causing this? I did check and I'm using the latest version of the ExcelXP tagset: "NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.130, 08/02/2013)"


Accepted Solutions
Solution
‎07-13-2015 09:28 AM
Occasional Contributor
Posts: 11

Re: ODS Tagsets ExcelXP Creating Multiple Workbooks

I found the solution by browsing "More Like This".  I'm not sure how, but my global setting for the newfile option must have been changed at some point.


I added this to my tagsets, newfile = none


For reference here is the thread I found the solution in:

https://communities.sas.com/message/119883#119883

View solution in original post


All Replies
Esteemed Advisor
Esteemed Advisor
Posts: 6,704

Re: ODS Tagsets ExcelXP Creating Multiple Workbooks

You may want to post what you have tried as:

ods tagsets.excelxp file="abc.xlsx";

/* First output */

ods tagsets.excelxp options=(sheet_name="first_sheet");

proc report...;

/* Next */

ods tagsets.excelxp options=(sheet_name="next_sheet");

proc report...;

ods tagsets.excelxp close;

Should produce one file with multiple tabs.  Also, its not a great idea to put dates in the filenames, what happens if you have more than one on the same day?

Occasional Contributor
Posts: 11

Re: ODS Tagsets ExcelXP Creating Multiple Workbooks

Here is the jist of what I have:

ods tagsets.ExcelXP path="&path.\Results\" (url=none)

    file="DATA &date_run..XLS"

    options(sheet_interval = "Table"

    sheet_label = " "

    sheet_name="Details"

    embedded_titles = "yes")

    style=Normal;

  proc report data=data nowd

     split='*'

     style(header) = {background=cx99ccff foreground=black font_size = 10pt FONT_WEIGHT = bold} style(column) = {font_size = 8pt}

     ;

  title1 "RESULTS FOR PAST TWO WEEKS";

.

.

.

  run;

  ods tagsets.Excelxp options(sheet_name="Unique IDs");

proc report data = uniqueids nowd

style(header) = {background=cx99ccff foreground=black font_size = 10pt FONT_WEIGHT = bold} style(column) = {font_size = 8pt};

.

.

.

run;

ods tagsets.excelxp close;

Esteemed Advisor
Esteemed Advisor
Posts: 6,704

Re: ODS Tagsets ExcelXP Creating Multiple Workbooks

Why do you have sheet_interval set?  That is for by-group processing.  Just specify a sheet_name option before each proc report.

Occasional Contributor
Posts: 11

Re: ODS Tagsets ExcelXP Creating Multiple Workbooks

I had different code initially but ended up just copying this from some SAS article to make sure it wasn't a mistake in my options.  Even without that I still have the same issue.

Super Contributor
Posts: 543

Re: ODS Tagsets ExcelXP Creating Multiple Workbooks

Perhaps this discussion may help

Anca.

Solution
‎07-13-2015 09:28 AM
Occasional Contributor
Posts: 11

Re: ODS Tagsets ExcelXP Creating Multiple Workbooks

I found the solution by browsing "More Like This".  I'm not sure how, but my global setting for the newfile option must have been changed at some point.


I added this to my tagsets, newfile = none


For reference here is the thread I found the solution in:

https://communities.sas.com/message/119883#119883

Post a Question
Discussion Stats
  • 6 replies
  • 1328 views
  • 0 likes
  • 3 in conversation