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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Vince_SAS
Rhodochrosite | Level 12

Try removing the Package result capability.

 

Vince DelGobbo

SAS R&D

View solution in original post

15 REPLIES 15
johnsville
Obsidian | Level 7
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
Fluorite | Level 6

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

Vince_SAS
Rhodochrosite | Level 12

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
Fluorite | Level 6

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
Rhodochrosite | Level 12

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
Fluorite | Level 6

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
Rhodochrosite | Level 12

Try removing the Package result capability.

 

Vince DelGobbo

SAS R&D

gantonaci
Fluorite | Level 6

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
Rhodochrosite | Level 12

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
Fluorite | Level 6

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
Rhodochrosite | Level 12

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
Fluorite | Level 6

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
Quartz | Level 8

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
Rhodochrosite | Level 12

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 15 replies
  • 5350 views
  • 5 likes
  • 5 in conversation