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)"
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:
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?
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;
Why do you have sheet_interval set? That is for by-group processing. Just specify a sheet_name option before each proc report.
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.
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:
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.