Stored Process - Output xlsx with multiple sheet to Excel client

[gantonaci]

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

[johnsville]

check sheet_interval options. default is table. You are using the same table. e.g. include sheet_interval='PROC' in the first ODS EXCEL statement's OPTIONS in your example

[gantonaci]

I got the same result. Both tables in the same sheet.

[Vince_SAS]

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

[gantonaci]

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;

[Vince_SAS]

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

[gantonaci]

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.

[Vince_SAS · Accepted solution]

Try removing the Package result capability.

Vince DelGobbo

SAS R&D

[gantonaci]

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

[Vince_SAS]

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

[gantonaci]

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

[Vince_SAS]

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

[gantonaci]

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.

[cuevasj]

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.

[Vince_SAS]

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