BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
garfield
Fluorite | Level 6

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)"

1 ACCEPTED SOLUTION

Accepted Solutions
garfield
Fluorite | Level 6

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

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

garfield
Fluorite | Level 6

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

garfield
Fluorite | Level 6

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.

AncaTilea
Pyrite | Level 9

Perhaps this discussion may help

Anca.

garfield
Fluorite | Level 6

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

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!

Discussion stats
  • 6 replies
  • 2533 views
  • 0 likes
  • 3 in conversation