topic Re: Stored Process - output to XLSX in ODS and Base Reporting

Stored Process - output to XLSX

AMUMOR_SAS — Wed, 12 Apr 2017 10:00:36 GMT

Hello,

We have several STP programs that uses the following output in Excel:

%if &Valg. = EXCEL %then %do;

data _null_;

/* Set HTTP headers */

rc = stpsrv_header('Content-type','application/vnd.ms-excel');

/* Prompt to SAVE or OPEN the attachment file named test.xls using Excel */

rc = stpsrv_header('Content-disposition','attachment; filename=filename.xls');

run;

%End;

What do I have to do to get a prompt to SAVE ot OPEN for XLSX?

Regards

Morten Fredrik Amundsen

Re: Stored Process - output to XLSX

Cynthia_sas — Wed, 12 Apr 2017 14:10:56 GMT

Hi:
There has to be more to your stored process than the content-type header. What destination are you using or what does the rest of your code look like? Are you using an ODSDEST override or using _WEBOUT? Are you using %STPBEGIN? How are you invoking this SP? In the Information Delivery Portal or using the Stored Process Web App? There is a separate forum for Stored Processes and you might find more help there. But they will want to know the same information. Just showing the content-type header is not enough information.

Cynthia

Re: Stored Process - output to XLSX

AMUMOR_SAS — Sat, 15 Apr 2017 09:24:41 GMT

Hi,

Yes, of course there is more. 🙂 I'm sorry that my information was'nt acurate enough.

I am using ODSDEST override, and %STPBEGIN and %STPEND. I invoke the STP using the Delivery Portal.

Regards

Morten Fredrik Amundsen

Re: Stored Process - output to XLSX

Cynthia_sas — Sat, 15 Apr 2017 13:26:13 GMT

And for the ODSDEST override, your destination is??? MSOFFICE2K? TAGSETS.EXCELXP? CSV?

I don't understand the rest of your question: "What do I have to do to get a prompt to SAVE ot OPEN for XLSX?" When I run a stored process that creates Excel output using TAGSETS.EXCELXP for example after I supply the prompts, the SP runs and I get prompted by the browser to either Open or Save the file. SAS doesn't control that popup window, it comes from the browser. I have some examples of code we used in the Stored Process class -- but they used TAGSETS.EXCELXP because that version of the class was before ODS EXCEL was available.

cynthia

My suggestion is that you try each of these SPs separately, as simple stored processes, outside of your fancy macro code. These only do a PROC PRINT with SASHELP.CLASS, no LIBNAME needed. You should be able to simply register these and then run them to see which one works best. (I like method #1 with _webout because if I have suboptions to pass in I think it is easier to use this method then %stpbegin.)

** Test 1: TAGSETS.EXCELXP with _Webout;
data _null_;
  rc = stpsrv_header('Content-type','application/vnd.ms-excel;');
  rc = stpsrv_header('Content-disposition','attachment; filename=try1.xls');
run;
ods listing close;

ods tagsets.excelxp body=_webout style=sasweb;

  proc print data=sashelp.class noobs;
    title 'test1 excelxp mimetype=application/vnd.ms-excel';
  run; 

ods tagsets.excelxp close;


** Test 2: TAGSETS.EXCELXP with %stpbegin;

%global _odsdest _odsstyle _odsstylesheet;

%let _odsdest=tagsets.excelxp;
%let _odsstyle=sasweb;
%let _odsstylesheet=;

data _null_;
  rc = stpsrv_header('Content-type','application/vnd.ms-excel;');
  rc = stpsrv_header('Content-disposition','attachment; filename=try2.xls');
run;

*ProcessBody;
%stpbegin;

  proc print data=sashelp.class noobs;
    title 'test2 excelxp mimetype=application/vnd.ms-excel';
  run; 

%stpend;


** Test 3: ODS EXCEL (if you have 9.4) with %stpbegin;

%global _odsdest _odsstyle _odsstylesheet;

%let _odsdest=excel;
%let _odsstyle=sasweb;
%let _odsstylesheet=;

data _null_;
  rc = stpsrv_header('Content-type','application/vnd.ms-excel;');
  rc = stpsrv_header('Content-disposition','attachment; filename=try3.xlsx');
run;

*ProcessBody;
%stpbegin;

  proc print data=sashelp.class noobs;
    title 'test3 ods excel mimetype=application/vnd.ms-excel';
  run; 

%stpend;

Re: Stored Process - output to XLSX

AMUMOR_SAS — Tue, 18 Apr 2017 07:24:01 GMT

Hello again,
When I run SP that creates Excel, I use destination MSOFFICE2K. I get prompted by the browser to either Open or Save the excel file. This creates the old excel format (xls).
I want to create the new excel format, xlsx. Which destination should I use to make it work?

Regards
Morten Fredrik Amundsen

Re: Stored Process - output to XLSX

AMUMOR_SAS — Tue, 18 Apr 2017 07:49:13 GMT

Hello,

When I am running your example Try3, I get prompted by the browser to eiter open or save the excel file. I choose save. When I then try to open it, I get an error message that says that either the file format or filetype is Invalid. Why do I get this message?

Regards

Morten Fredrik

Re: Stored Process - output to XLSX

AMUMOR_SAS — Tue, 18 Apr 2017 10:44:57 GMT

Hello again,

I have tryed all three examples that you did post me. Both Example1 and Example2 where successful, but Example3, in which the excel file Try3 was created, was unsuccessful.

The excel file Try3 is created, but it is impossible to open. As I mentioned earlier, I get an error message which tells me that either the file format or file type is invalid. How come?

Regards Morten Fredrik

Re: Stored Process - output to XLSX

BrunoMueller — Tue, 18 Apr 2017 13:23:28 GMT

Have a look here https://communities.sas.com/t5/SAS-Stored-Processes/ODS-EXCEL-in-Web-StoredProcess/td-p/286083

Basicaly you will use the follwoing code:


data _null_;
  rc = stpsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  rc = stpsrv_header('Content-disposition','attachment; filename=try3.xlsx');
run;

ods excel file = _webout style=Plateau;
proc print data=sashelp.cars;
run;
ods excel close;

make sure you do not have the %stpbegin and %stpend somewhere in your code.

Bruno

Re: Stored Process - output to XLSX

AMUMOR_SAS — Tue, 18 Apr 2017 13:32:10 GMT

Hi,

Thanks for all help.

Regards

Morten Fredrik

Re: Stored Process - output to XLSX

niko_gallo — Tue, 26 Jun 2018 11:07:54 GMT

...waiting message...

how to post a waiting message in STP?

Displaying the "Please Wait" message for a long-running stored process

Re: Stored Process - output to XLSX

AMUMOR_SAS — Tue, 26 Jun 2018 11:19:57 GMT

Hi niko_gallo,

Thanks very much for this "Please Wait" Example. I'll test it.

Regards

Morten Fredrik

Re: Stored Process - output to XLSX

niko_gallo — Tue, 26 Jun 2018 12:20:50 GMT

hi Morten,

my SAS code is similar...is:

ods excel file=_webout style=plateau;

data _null_;
rc = stpsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
rc = stpsrv_header("Content-disposition","attachment; filename=&_name_report._&_dt_report_ymd..XLS");
run;

proc print data =&_flnm. noobs label ;run;
ods excel close;

Re: Stored Process - output to XLSX

Cynthia_sas — Tue, 26 Jun 2018 13:15:50 GMT

Hi:
Your Content Type DATA _NULL_ step must be BEFORE the ODS EXCEL statement. Look again at Bruno's code in the solution to the post.
Cynthia

Re: Stored Process - output to XLSX

niko_gallo — Tue, 26 Jun 2018 14:46:51 GMT

hi chintia,

the following solution it's wrong! because print the xls on the webpage...

data _null_;
     file _webout;
     put '<html>';
     put '<body>';
     put '<div id="pleasewait">';
     put '<h2>Please Wait. Your request is being processed.</h2>';  
     put '</div>';  
     do i = 1 to 10;
       put "                                       ";
     end;
   run;

data _null_;
  rc = stpsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
  rc = stpsrv_header('Content-disposition','attachment; filename=try3.xlsx');
run;

ods excel file = _webout style=Plateau;
proc print data=sashelp.cars;
run;
ods excel close;

this is the result:

Re: Stored Process - output to XLSX

Cynthia_sas — Tue, 26 Jun 2018 15:19:00 GMT

HI:
In your original question, you posted ODS EXCEL code. The original question that I answered was only about the placement of your Content-type header needing to be outside of the ODS EXCEL call.

You've added extra code that sends a waiting message, as illustrated in this Tech Support note:
http://support.sas.com/kb/30/889.html and my guess is that you have some kind of timing issue. It is possible that your Stored Process doesn't actually NEED a waiting message and so the ODS EXCEL results are coming back to the browser WITH the waiting message. This would be something you need to work on with Tech Support. Someone needs to look at ALL of your code and ALL of your stored process metadata and understand exactly HOW you are submitting the stored process (from a client application, from the Add-in for Microsoft Office, from the Stored Process Web Application...).

To submit a track to Tech Support, you can fill out this form http://support.sas.com/ctx/supportform/createForm and reference this community forum posting in your mail to them with the other required information.

Cynthia

Re: Stored Process - output to XLSX

niko_gallo — Tue, 26 Jun 2018 15:58:28 GMT

hi Cynthia,

i submit my STP from SAS portal (Windows IE) and this is the code:

*ProcessBody;
ods excel file=_webout style=plateau;

data _null_;
rc = stpsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
rc = stpsrv_header("Content-disposition","attachment; filename=report.XLS");
run;

proc print data =report (drop = dtreport) noobs label ;run;
ods excel close;

I do not have any problem with this sas-code...so...i want to insert the waiting message between when i submit the STP and when i save the output in xls format.

regards

Niko

Re: Stored Process - output to XLSX

Cynthia_sas — Tue, 26 Jun 2018 17:56:53 GMT

Hi, you have 2 different types of output coming to the browser. You have an HTML-based waiting message and an Excel XML-based workbook. Therefore, you need 2 "pipelines" from the server back to the browser. One of those "pipelines" will need to have an Content-type header for the HTML waiting message, then the second "pipeline" will return the ODS Excel output as long as the Content-type header signals that the second pipeline is going to be XML that needs to be handled differently than the HTML.

I still think your best resource is opening a track with Tech Support.

Cynthia

Re: Stored Process - output to XLSX

niko_gallo — Tue, 26 Jun 2018 22:27:43 GMT

Ooook

Re: Stored Process - output to XLSX

niko_gallo — Mon, 23 Jul 2018 13:31:49 GMT

hi,

i have a problem with ouptut xlsx with a stored process server.

any suggestion?
thanks in advance

SAS-CODE

ods excel options(sheet_name="&_report_name.") file=_webout style=plateau;

data _null_;

rc = stpsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
rc = stpsrv_header("Content-disposition","attachment; filename=&_report_name._&_dt_report_ymd..XLSX");
run;

proc print data =&_report_name. noobs label ;run;
ods excel close;

LOG STORED PROCESS

ERROR: The SAS System stopped processing this step because of insufficient memory.
NOTE: There were 283923 observations read from the data set WORK.CENTRISC.
NOTE: The PROCEDURE PRINT printed pages 1-977.
NOTE: PROCEDURE PRINT used (Total process time):
real time 1:54.75
cpu time 1:48.84

ERROR: An exception has been encountered.
Please contact technical support and provide them with the following traceback information:

Re: Stored Process - output to XLSX

Cynthia_sas — Mon, 23 Jul 2018 15:13:14 GMT

Hi:
The error message explains that the step failed because of insufficient memory and looks like you got traceback information for Tech Support. Working with Tech Support seems your best resource for this issue.
Cynthia