The SAS Output Delivery System and reporting techniques

ODS to a .xls file and output to web at same time ?

Reply
Contributor
Posts: 33

ODS to a .xls file and output to web at same time ?

Hi,

I have a stored process which works fine when I use ODS and output to a .xls file. But I also wanted to provide a preview of the data on the script so I just did a PROC PRINT like I do normally but I get the following error when I have to set the output properties in the STP. It works ok outputting to the web page or .xls but not both in the same STP?


MPRINT(TEST): ods html file="e:\My.xls";
NOTE: Writing HTML Body file: e:\My.xls
ERROR: A component of C:\DOCUME~1\sassrv\LOCALS~1\Temp\SAS Temporary Files\_TD3088\Prc70\45c2a8d7-4bb6-46ee-bb01-caef2047cc28\e:\My.xls is
not a directory.
WARNING: No body file. HTML output will not be created.
MPRINT(TEST): proc print data = test.test_print;
MPRINT(TEST): var F1 F2 F3 F4 F5 F6;
MPRINT(TEST): run;

WARNING: No output destinations active.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

Thanks
Lee Message was edited by: Lee2010
Super Contributor
Super Contributor
Posts: 3,174

Re: ODS to a .xls file and output to web at same time ?

Posted in reply to LeeJenson
Suggest you contact SAS tech support and open a track with this type of error condition.

Scott Barry
SBBWorks, Inc.
Contributor
Posts: 33

Re: ODS to a .xls file and output to web at same time ?

will do

Lee
Regular Contributor
Regular Contributor
Posts: 156

Re: ODS to a .xls file and output to web at same time ?

Posted in reply to LeeJenson
Dear Lee,

this should work in the same STP.
Did you open ods html as well? And did you define a body for that? The error says there's no body file.

Best regards
Eva
SAS Super FREQ
Posts: 8,868

Re: ODS to a .xls file and output to web at same time ?

Posted in reply to LeeJenson
Hi:
Are you really running a SAS Stored Process in the context of the SAS Enterprise Intelligence Platform or are you calling your .SAS program a Stored Process??? There's a HUGE difference.

Before you call Tech Support, but there are some underlying facts that will impact what you tell them:
1) ODS HTML creates an HTML file -- no matter what file extension you give the file, it is still HTML. A file extension of .XLS only serves to fool the Windows registry into launching Excel when you double click on the output in a SAS Results Windows. Tech Support needs to know whether you are running your stored process from within the SAS Add-in for Microsoft Office, SAS Enterprise Guide or the SAS Information Delivery Portal. The code that you show is not the correct way to code a SAS Enterprise Intelligence Stored Process.

2) A stored process can only send results back to a client application as:
-- streaming (following an HTTP protocol using a special FILEREF known as _WEBOUT
-- transient package (written to a temporary cache file as a zip-like archive)
-- permanent package (written to a webDAV location) OR, if configured correctly, can be written to a local file system. The method you're using
[pre]
ods html file="e:\My.xls";
[/pre]

...is NOT the correct method for coding a stored process and designating results. For one thing, unless there is an "E" drive attached to the Stored Process Server or the Workspace Server, the stored process cannot write directly to this location. If the "E" drive is attached to YOUR personal machine, neither the Stored Process Server or the Workspace server have any visibility of your personal machine. For another thing, when you register your stored process in the Metadata you tell the Metadata server what type of result type your stored process will return. (see below)

3) A stored process can ONLY return one type of output. Generally, the client application (Word, Excel, EG, Web Report Studio, Portal) has a specific way to deal with output and accept or receive and render stored process results. When you talk to Tech Support, you will have to tell them WHAT client application you are using to receive the stored process results. For example:
Word: receives ONLY HTML, SASReport XML and RTF
Excel: receives ONLY: HTML, SASReport XML and CSV
PPT: receives ONLY: SASReport XML
WRS: receives ONLY: SASReport XML
Information Delivery Portal: Can receive ANY type: HTML, SASReport XML, RTF, PDF, .XLS (from ODS TAGSETS.EXCELXP) -- however, you need to use the STPSRV_HEADER function with these stored processes to set the correct "content-type" header for the Portal
EG: receives ONLY: SASReport XML, HTML, RTF, PDF

4) Your code does not use _WEBOUT -- which is one possibility for coding an SP for use on the Portal; for other client applications your code does not have %STPBEGIN/%STPEND or *ProcessBody; -- as required by the servers on the Enterprise Intelligence Platform.

5) Finally, I don't understand what you mean when you say you "wanted to provide a preview of the data on the script so I just did a PROC PRINT like I do normally". A stored process program is different from a batch SAS job. For example, in Batch SAS, you could do this:
[pre]
** step 1;
ods html file='e:\send_to_browser.html' style=sasweb;
...Proc Print code;
ods html close;

** step 2;
ods html file='e:\open_with_Excel.xls';
.... other code;
ods html close;
[/pre]

and create two PHYSICAL files from within 1 program. However, you cannot just convert a program like this to 1 stored process unless you are using packages as the output type -- which you have not coded.

You still need to work with Tech Support, but they will need to know the following:
-- Are you really talking about a SAS Stored Process which runs on the SAS Enterprise Intelligence Platform? And if so, then these answers as well:
-- How did you create your stored process
-- How did you register your stored process
-- What is the execution information for your stored process (including the result type that you specified -- streaming, transient, or permanent package output
-- where is your stored process executing -- stored process server or workspace server?
-- what client application(s) will you use to execute your stored process
-- what is your FULL stored process code??

cynthia
Occasional Contributor
Posts: 12

Re: ODS to a .xls file and output to web at same time ?

Posted in reply to Cynthia_sas

Hi Cynthia_SAS,

I am facing similar issue. I have created 10 stored procedure for my client portal and now i am facing issue with the output. By default we want output to be directed automatically in excel in temporary table. now if user wants to save it then he/she will save it, otherwise dicard it.

How should i go for it.

As code is stored in server and stored procedure is also created on server. Do we still need to have a physical excel file on server or we have other option too ?

 

Please suggest any way out to resolve it.

 

Thanks in advance.

SAS Super FREQ
Posts: 8,868

Re: ODS to a .xls file and output to web at same time ?

Hi:
You posted your question in the middle of a 7 year old posting. It would be better to do the following:
1) start a new post and refer to this post in your question by copying the URL for this post and
2) post your question in the forum reserved for Stored Processes.

Are you running a stored process or a stored RDBMS procedure? SAS has stored processes. Are you using the SAS Add-in for Microsoft Office -- using a stored process in that client application would give you great flexibility for the SP results to come back to Excel, but then under the user's control for whether they want to save or not.

Also, I would recommend working with Tech Support -- ALL of your stored process code, or at least 1 representative example has to be available for viewing. Just saying you have 10 stored processes isn't enough. You say you are using a "client portal" but is it the SAS Information Delivery Portal? You indicated that you wanted the output automatically directed to excel: there are 2 ways to do this: 1) use the Excel client in the SAS Add-in for Microsoft Office or 2) use the Stored Process Web App with the appropriate code to make an Excel file and the appropriate content-type headers to be executed on the server but have the results sent to Excel.

cynthia
here's the location of the Stored Process Forum:
https://communities.sas.com/t5/SAS-Stored-Processes/bd-p/sas_stored_processes
New Contributor
Posts: 3

Re: ODS to a .xls file and output to web at same time ?

Posted in reply to LeeJenson
Hi,
The following is taken from http://support.sas.com//rnd/base/ods/templateFAQ/ODS91.pdf

It talked about an issue similar to yours. Hope helps.


Setting a Default Path for HTML
You can set a default path for both the local files that get generated with ODS and well as the HTML files that get generated as a result of generating HTML by default. This is done by going to the Results tab
(Tools->Options->Preferences->Results ) and selecting HTML. The Folder box is used to either select or specify the folder that you want your HTML files to appear. Note: this selects the location for the default HTML files created and HTML file created with the ODS HTML statement. If you create HTML using ODS
HTML and you specify a path for the file, an error will error. This is because we prepend the path specified for the folder with the path specified with ODS. To specify a different path than indicated in the folder of the Results tab, use the ODS HTML PATH= option with the URL=NONE sub-option.
Below is the error that occurs if this is not done.
1 ods html file="c:\temp\temp.html";
NOTE: Writing HTML Body file: c:\temp\temp.html
ERROR: A component of c:\test\c:\temp\temp.html is not a directory.
WARNING: No body file. HTML output will not be created.
NOTE: Writing HTML Body file: sashtml1.htm
run;
Contributor
Posts: 33

Re: ODS to a .xls file and output to web at same time ?

Posted in reply to ztan42885
Hi,

Thank you for your input on this thread.

I will investigate some more with the information you supplied.

Thanks
Ask a Question
Discussion stats
  • 8 replies
  • 871 views
  • 1 like
  • 6 in conversation