I've looked through a few topics like this one, but unfortunately they all seem to be addressing this same problem when running from a webservice. I'm running my Stored Process from Excel 2013 using SAS Add-In for Microsoft Office 7.100.5.6165.
I'm using SAS 9.4M5.
I can get the tables with the following code but they show on the same sheet.
I have an output stream called OUTSTR, but I'm not sure it makes any difference.
Sample code as follow:
* Begin EG generated code (do not edit this line);
*
* Stored process registered by
* Enterprise Guide Stored Process Manager V7.1
*
* ====================================================================
* Stored process name: Stored Process for teste
* ====================================================================
*;
*ProcessBody;
%STPBEGIN;
* End EG generated code (do not edit this line);
ODS EXCEL FILE=OutStr OPTIONS(SHEET_NAME="Class");
proc print data=sashelp.class;
run;
ODS EXCEL OPTIONS(SHEET_NAME="Cars");
proc print data=sashelp.CARS;
run;
ODS EXCEL CLOSE;
* Begin EG generated code (do not edit this line);
;*';*";*/;quit;
%STPEND;
* End EG generated code (do not edit this line);
I got the same result. Both tables in the same sheet.
I haven't tested your code but here is what I think is happening:
The STPBEGIN macro opens the HTML destination and directs the output to the _WEBOUT reserved FILEREF.
The EXCEL destination creates a multi-sheet Excel workbook, but it is directed to the output stream, and not returned to the client.
The STPEND macro closes all open ODS destinations. The output that was written to _WEBOUT - HTML created by the two instances of PROC PRINT - is returned to the client and displayed as a single sheet because it is HTML output.
Try this code instead, exactly as it is shown, without STPBEGIN/STPEND:
ods _all_ close;
ods Excel file=_webout options(sheet_name='Class');
proc print data=sashelp.class; run; quit;
ods excel options(sheet_name='Cars');
proc print data=sashelp.cars; run; quit;
ods Excel close;
Vince DelGobbo
SAS R&D
I received this error:
14 +ods _all_ close; 15 + 16 +ods Excel file=_webout options(sheet_name='Class'); ERROR: Logical name is not available. ERROR: No body file. EXCEL output will not be created. 17 + 17 !+ proc print data=sashelp.class; 18 + run;
Is the stored process registered to create "streaming" output? That is what is needed for the _WEBOUT reserved FILEREF to be assigned.
You don't show your whole log - are you sure that STPBEGIN/STPEND are not included? You likely need to deselect the option to include the macros during a step in the SAS Enterprise Guide Stored Process Wizard.
Vince DelGobbo
SAS R&D
Sorry, didn't want to clutter the answer and thought the rest was unnecessary (however I just noticed another error that may help explain).
I have one output stream. It is "XML based data" with "text/xml" content type.
Fileref is OUTSTR with "Allow rewinding stream" checked.
The stored process has Stream and Package result capabilities checked.
11 The SAS System 11:45 Friday, December 14, 2018
NOTE: Copyright (c) 2016 by SAS Institute Inc., Cary, NC, USA.
NOTE: SAS (r) Proprietary Software 9.4 (TS1M5)
Licensed to TRIBUNAL DE CONTAS DA UNIAO, Site 70200868.
NOTE: This session is executing on the Linux 4.1.12-124.16.3.el7uek.x86_64 (LIN X64) platform.
NOTE: Updated analytical products:
SAS/STAT 14.3
SAS/ETS 14.3
NOTE: Additional host information:
Linux LIN X64 4.1.12-124.16.3.el7uek.x86_64 #2 SMP Wed Jun 13 07:40:34 PDT 2018 x86_64 Oracle Linux Server release 7.5
You are running SAS 9. Some SAS 8 files will be automatically converted
by the V9 engine; others are incompatible. Please see
http://support.sas.com/rnd/migration/planning/platform/64bit.html
PROC MIGRATE will preserve current SAS file attributes and is
recommended for converting all your SAS libraries from any
SAS 8 release to SAS 9. For details and examples, please see
http://support.sas.com/rnd/migration/index.html
This message is contained in the SAS news file, and is presented upon
initialization. Edit the file "news" in the "misc/base" directory to
display site-specific news and information in the program log.
The command line option "-nonews" will prevent this display.
NOTE: SAS Initialization used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: The autoexec file, /sasconfig/Lev1/SASApp/WorkspaceServer/autoexec.sas, was executed at server initialization.
>>> SAS Macro Variables:
SYSDBMSG=ODBC: [DataDirect][ODBC lib] Data source name not found and no default driver specified
SYSDBRC=IM002
_APSLIST=_RESULT,_CLIENTVERSION,_CLIENTMACHINE,_ODSDEST,_ODSSTYLESHEET,_ODSOPTIONS,_TIMEZONE,_GOPT_DEVICE,_CLIENTUSERID,_MSOFFICECLI
ENT,_CLIENTNAME,_CLIENTUSERNAME,_METAUSER,_ODSM_SR_RAW,_ENCODI
NG,_ODSSTYLE,_METAPERSON,_ARCHIVE_PATH,_METAFOLDER,_PROGRAM,_CLIENT,_USERNAME,_SECUREUSERNAME
_ARCHIVE_PATH=TEMPFILE
_CLIENT=SAS Add-In for Microsoft Office; CLR 4.0.30319.42000; Microsoft Windows NT 6.1.7601 Service Pack 1
_CLIENTMACHINE=E-087408
_CLIENTNAME=SAS Add-In for Microsoft Office
_CLIENTUSERID=antonaci
_CLIENTUSERNAME=Giuseppe de Abreu Antonaci
_CLIENTVERSION=7.100.5.6165
_ENCODING=UTF8
_GOPT_DEVICE=ACTIVEX
_METAFOLDER=/User Folders/antonaci(1)/My Folder/
2 The SAS System 11:45 Friday, December 14, 2018
_METAPERSON=antonaci
_METAUSER=antonaci
_MSOFFICECLIENT=Excel
_ODSDEST=tagsets.sasreport13
_ODSM_SR_RAW=on
_ODSOPTIONS=options(rolap="on")
_ODSSTYLE=EGDefault
_ODSSTYLESHEET=(URL="file:///D:/Program%20Files/SASHome/x86/SASAddinforMicrosoftOffice/7.1/Styles/AMODefault.css")
_PROGRAM=/User Folders/antonaci(1)/My Folder/Stored Process for teste
_REPLAY="&_URL?_sessionid=00000000-0000-0000-0000-000000000000&_program=replay&_entry=&_TMPCAT.."
_RESULT=PACKAGE_TO_ARCHIVE
_SECUREUSERNAME=(Process)
_TIMEZONE=GMT-02:00
_TMPCAT=APSWORK.TCAT0000
_USERNAME=(Process)
NOTE: %INCLUDE (level 1) file /sasdata/usuarios/antonaci/Credibilidade_teste/Stored_Process_for_teste.sas is file /sasdata/usuarios/antonaci/Credibilidade_teste/Stored_Process_for_teste.sas.
3 +ods _all_ close;
4 +
5 +ods Excel file=_webout options(sheet_name='Class');
The SAS System
ERROR: Logical name is not available.
ERROR: No body file. EXCEL output will not be created.
6 +
7 +proc print data=sashelp.class; run;
NOTE: SAS set option OBS=0 and will continue to check statements. This might cause NOTE: No observations in data set.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
7 !+ quit;
8 +
9 +ods excel options(sheet_name='Cars');
10 +
11 +proc print data=sashelp.cars; run;
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
11 !+ quit;
12 +
13 +ods Excel close;
ERROR: Insufficient authorization to access /sasconfig/Lev1/SASApp/sasexcl.xlsx.
NOTE: %INCLUDE (level 1) ending.
Try removing the Package result capability.
Vince DelGobbo
SAS R&D
Almost!! But I think we are getting somewhere.
Removed the Package result capability.
First, I had to add a third proc print for reasons you will soon understand.
ods _all_ close;
ods Excel file=_webout options(sheet_name='Class');
proc print data=sashelp.class; run; quit;
ods excel options(sheet_name='Cars');
proc print data=sashelp.cars; run; quit;
ods excel options(sheet_name='Airline');
proc print data=sashelp.airline; run; quit;
ods Excel close;
Doesn't matter where I specify my result output I get two outputs in Excel.
If I select "new workbook" the first output is a new workbook called Workbook8 (or 5, 6, 7 in previous tests, just regular name for new workbook), with one sheet called "Stored Process for teste" and with the "Class" data in it. This is just a opened Excel, there are no files and I have to save it to keep it.
If I select "new worksheet" and specify its name I get the same result in a new worksheet. If I specify a range the same result in the specified range.
The other output is called "Main.html". It opens in Excel and it has two sheets called "Cars" and "Airlines" with their respective data. This one is an html file saved in a temporary folder "\<user>\Documents\My SAS Files\Add-In for Microsoft Office\_SOA_A5XKMNSB.BC0003I6_601538504"
In the same folder there is a file called "main.log" that I pasted here, maybe it will give you some insight.
As a last note, I tried deleting the Data Targets output stream, but it changed nothing.
t11 The SAS System 14:03 Friday, December 14, 2018 t n NOTE: Copyright (c) 2016 by SAS Institute Inc., Cary, NC, USA. n NOTE: SAS (r) Proprietary Software 9.4 (TS1M5) n Licensed to TRIBUNAL DE CONTAS DA UNIAO, Site 70200868. n NOTE: This session is executing on the Linux 4.1.12-124.16.3.el7uek.x86_64 (LIN X64) platform. d d d n NOTE: Updated analytical products: n n SAS/STAT 14.3 n SAS/ETS 14.3 n n NOTE: Additional host information: n n Linux LIN X64 4.1.12-124.16.3.el7uek.x86_64 #2 SMP Wed Jun 13 07:40:34 PDT 2018 x86_64 Oracle Linux Server release 7.5 n d You are running SAS 9. Some SAS 8 files will be automatically converted d by the V9 engine; others are incompatible. Please see d http://support.sas.com/rnd/migration/planning/platform/64bit.html d d PROC MIGRATE will preserve current SAS file attributes and is d recommended for converting all your SAS libraries from any d SAS 8 release to SAS 9. For details and examples, please see d http://support.sas.com/rnd/migration/index.html d d d This message is contained in the SAS news file, and is presented upon d initialization. Edit the file "news" in the "misc/base" directory to d display site-specific news and information in the program log. d The command line option "-nonews" will prevent this display. d d d d n NOTE: SAS Initialization used (Total process time): n real time 0.00 seconds n cpu time 0.00 seconds n n NOTE: The autoexec file, /sasconfig/Lev1/SASApp/WorkspaceServer/autoexec.sas, was executed at server initialization. d d >>> SAS Macro Variables: d d SYSDBMSG=ODBC: [DataDirect][ODBC lib] Data source name not found and no default driver specified d SYSDBRC=IM002 d d _APSLIST=_RESULT,_CLIENTVERSION,_CLIENTMACHINE,_ODSDEST,_ODSSTYLESHEET,_ODSOPTIONS,_TIMEZONE,_GOPT_DEVICE,_CLIENTUSERID,_MSOFFICECLI d ENT,_CLIENTNAME,_CLIENTUSERNAME,_METAUSER,_ODSM_SR_RAW,_ENCODI d NG,_ODSSTYLE,_METAPERSON,_METAFOLDER,_PROGRAM,_CLIENT,_USERNAME,_SECUREUSERNAME d _CLIENT=SAS Add-In for Microsoft Office; CLR 4.0.30319.42000; Microsoft Windows NT 6.1.7601 Service Pack 1 d _CLIENTMACHINE=E-087408 d _CLIENTNAME=SAS Add-In for Microsoft Office d _CLIENTUSERID=antonaci d _CLIENTUSERNAME=Giuseppe de Abreu Antonaci d _CLIENTVERSION=7.100.5.6165 d _ENCODING=UTF8 d _GOPT_DEVICE=ACTIVEX d _METAFOLDER=/User Folders/antonaci(1)/My Folder/ d _METAPERSON=antonaci t12 The SAS System 14:03 Friday, December 14, 2018 t d _METAUSER=antonaci d _MSOFFICECLIENT=Excel d _ODSDEST=tagsets.sasreport13 d _ODSM_SR_RAW=on d _ODSOPTIONS=options(rolap="on") d _ODSSTYLE=EGDefault d _ODSSTYLESHEET=(URL="file:///D:/Program%20Files/SASHome/x86/SASAddinforMicrosoftOffice/7.1/Styles/AMODefault.css") d _PROGRAM=/User Folders/antonaci(1)/My Folder/Stored Process for teste d _REPLAY="&_URL?_sessionid=00000000-0000-0000-0000-000000000000&_program=replay&_entry=&_TMPCAT.." d _RESULT=STREAM d _SECUREUSERNAME=(Process) d _TIMEZONE=GMT-02:00 d _TMPCAT=APSWORK.TCAT0000 d _USERNAME=(Process) d n NOTE: %INCLUDE (level 1) file /sasdata/usuarios/antonaci/Credibilidade_teste/Stored_Process_for_teste.sas is file /sasdata/usuarios/antonaci/Credibilidade_teste/Stored_Process_for_teste.sas. s 3 +ods _all_ close; s 4 + s 5 +ods Excel file=_webout options(sheet_name='Class'); t1 The SAS System t s 6 + s 7 + s 7 !+ proc print data=sashelp.class; run; n n NOTE: There were 19 observations read from the data set SASHELP.CLASS. n NOTE: PROCEDURE PRINT used (Total process time): n real time 0.05 seconds n cpu time 0.05 seconds n n s 7 !+ quit; s 8 + s 9 +ods excel options(sheet_name='Cars'); s 10 + s 11 + s 11 !+ proc print data=sashelp.cars; run; n n NOTE: There were 428 observations read from the data set SASHELP.CARS. n NOTE: PROCEDURE PRINT used (Total process time): n real time 2.36 seconds n cpu time 2.37 seconds n n s 11 !+ quit; s 12 + s 13 +ods excel options(sheet_name='Airline'); s 14 + s 15 + s 15 !+ proc print data=sashelp.airline; run; n n NOTE: There were 144 observations read from the data set SASHELP.AIRLINE. n NOTE: PROCEDURE PRINT used (Total process time): n real time 0.24 seconds n cpu time 0.25 seconds n n s 15 !+ quit; s 16 + t1 The SAS System t s 17 +ods
I looked through my notes and found that we made changes to STPBEGIN in SAS 9.4M5 and SAS Add-In for Microsoft Office 8.0 to support the Excel destination. Based on your log you are using an old release of the SAS Add-in for Microsoft Office:
_CLIENTVERSION=7.100.5.6165
You need to update your version of the SAS Add-In for Microsoft Office for this to work.
Then set the Package result property on your stored process, and try this code:
*;
* Package result capability required. Does not
* work with only Stream result.
*;
%let _ODSDEST=Excel;
%let _ODSSTYLE=HTMLBlue;
%let _ODSOPTIONS=options(sheet_name='Class');
%STPBEGIN()
proc print data=sashelp.class; run; quit;
ods excel options(sheet_name='Cars');
proc print data=sashelp.cars; run; quit;
ods excel options(sheet_name='Retail');
proc print data=sashelp.retail; run; quit;
%STPEND()
Vince DelGobbo
SAS R&D
Updated it to version 7.15 HF7 (7.100.5.6182) (32-bit).
It almost worked, but I missing a file extension.
I get the folowing error at the end:
The SAS Add-In for Microsoft Office cannot display the results that were returned.
The results are in a format that is not supported by Microsoft Excel. The results have been saved to "D\<user>\Documents\My SAS Files\Add-In for Microsoft Office\_SOA_A5XKMNSB.BC0003I6_341916613\main"
In that folder there is a file "main" without extension and "main.log".
If I change the name of "main" to "main.xlsx" and open it, it works and it is perfect. All three sheets with their names and HTMLBlue style.
The main.logis attached (had to change the extension to txt).
You need to upgrade to version 8 of the SAS Add-in for Microsoft Office.
My working version is:
_CLIENTVERSION=8.0.1.2022
Vince DelGobbo
SAS R&D
After removing the package result capabilities the following code did the trick (not perfect, but good enough).
%global _odsdest _odsoptions ;
%let _odsdest = tagsets.excelXP;
%let _odsoptions =;
%let _odsstyle = HTMLBlue;
%let rv = %sysfunc(appsrv_header(Content-type,application/vnd.ms-excel));
ods tagsets.ExcelXP file=_webout style=HTMLBlue options(sheet_name='Class');
proc print data=sashelp.class; run; quit;
ods tagsets.ExcelXP options(sheet_name='Cars');
proc print data=sashelp.cars; run; quit;
ods tagsets.ExcelXP options(sheet_name='Airline');
proc print data=sashelp.airline; run; quit;
ods tagsets.ExcelXP close;
I get a xml that automatically opens in Excel with all sheets and data.
I will try the other solution, but I still need to get my IT department to update the SAS Add-in, so that may take a while.
Thanks for all the help.
What if I want to mix a table with a proc sgplot graph? Can I output both of those on the same worksheet ?
Using ods tagsets.excelxp I cannot get the graph to print, nor can I get the extension "xlsx" on the file.
if i use
ods excel file="destination.xlsx";
I can get both the chart and graph to print, but when I try to put that into a stored process that is called by VA, I get an error.
The ODS ExcelXP tagset does not support graphic images and only creates a Microsoft Excel XML Spreadsheet file.
The ODS Excel destination supports graphic images and creates Microsoft XLSX files. This paper shows an example of a chart and table in the same worksheet:
New for SAS® 9.4: Including Text and Graphics in Your Microsoft Excel Workbooks, Part 2
https://support.sas.com/resources/papers/proceedings17/SAS0127-2017.pdf
What error do you get when you run the code in VA? Can you post the SAS log?
Vince DelGobbo
SAS R&D
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: