CODEWITHSUNDEEP
sas
user601828
user601828

Reputation: 509

exporting sas stored process output

I created a stored process but I want to export the output to Excel. My usual export statement doesn't work in the stored process.

%let _ODSDEST=none;
%STPBEGIN();
data x;
  set sashelp.class;
run;

proc export data=x outfile = "//my documents/sp_test.xlsx" dbms=xlsx replace;     
  sheet="table1"; 
run; 
* Begin EG generated code (do not edit this line); 
;*';*";*/;quit; 
%STPEND;

Is there a way to get this to work in the stored process?

Upvotes: 0

Views: 1619

Answers (3)

Allan Bowe
Allan Bowe

Reputation: 12691

You can also try setting your STP up as a streaming web service, removing the %STPBEGIN and %STPEND macros, and sending to _webout using this macro: https://core.sasjs.io/mp__streamfile_8sas.html

The benefit of this, is that your code will subsequently work on Viya as well.

Upvotes: 0

Quentin
Quentin

Reputation: 6378

One way to have a stored process return an excel file (actually in this case it's an xml file that excel will happily open) is to use ODS to output tagsets.excelxp (xml).

When you do this, you can use stpsrv_header to modify the HTML header. The first statement tells the browser to open the file with excel, the second tells it the file name. I believe for this header modification to work the stored process needs to deliver streaming results, not package results. But I could be wrong.

When I run below, I get a file download dialog box from the browser, allowing me to open or save the file. I'm running from Stored Process Web App, but should work fine when called from Information Delivery Portal.

%let _odsdest=tagsets.excelxp;
%let rc=%sysfunc(stpsrv_header(Content-type,application/vnd.ms-excel));
%let rc=%sysfunc(stpsrv_header(Content-disposition,attachment%str(;) filename=MyExcelFile.xls));

%stpbegin()
proc print data=sashelp.shoes (obs=&obs);
run;
%stpend()

Upvotes: 1

Ramu
Ramu

Reputation: 165

did u check with your spelling proc exportd and outfile='mypath/my documents/myoutpt.xlsx' dbms=xlsx or outfile='mypath/my documents/myoutpt.xls' dbms=xls?? U can try with ODS also.

Upvotes: 0

Related Questions