topic Re: Export excel using stored process in Developers

Export excel using stored process

GeorgeGiannako — Tue, 27 Sep 2016 07:37:06 GMT

Good morning everyone,

My company is using SAS version 9.2 (TS2M3).

I have a data table which I want to make available to users through browser. The thought is to create a stored process which will create a prompt to users to download an Excel file of the data table.

I have tried other solutions like PROC EXPORT but it is not working. The client sees an empty browser page when he chooses the stored process on the SAS portal.

This was the most recent code which I used but with no results.

PROC EXPORT
DATA=VA_WORK.WRK_TEST_GL_CLAIMS
DBMS=CSV
OUTFILE="c:\gl_claims.csv"
REPLACE;
PUTNAMES=NO;
RUN;

My question is how to create a stored process, where users will be able to download a dataset into an excel file.

Thank you in advance,

George

Re: Export excel using stored process

RW9 — Tue, 27 Sep 2016 08:30:15 GMT

Well, I don't use Stored Processes so can't help there, however I will say that CSV != Excel. CSV stands for Comma Separated Variable file, and is a text based delimited file format. Excel doeshave some inbuilt functionality to process this data if the file extension is associated with Excel, however that isn't default behaviour. Browsers don't have this functionality, so it should just show up as plain text in the browser. As to why its not showing up, well a guess would be you are exporting the data to a file on your local machine - the C: drive - how will the web page know where this file is? The web page would need a url to the file.

Re: Export excel using stored process

GeorgeGiannako — Tue, 27 Sep 2016 08:39:00 GMT

I see. However, changing the csv to xls or xlsx does not work either.

I used some other piece of code i found in the community but it does not work either BUT it downloads a corrupted excel file.

*ProcessBody;
data _null_;

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

run;

/*ods tagsets.excelxp file= "c:\report.xml";
Proc tabulate data=GL_Claims.xml;
run;
ods tagsets.excelxp close;*/

%let _ODSDEST=TAGSETS.EXCELXP;

/* Create output */
%stpbegin;

  proc print data=VA_WORK.WRK_TEST_GL_CLAIMS;
  run;

%stpend;

Re: Export excel using stored process

Kurt_Bremser — Tue, 27 Sep 2016 08:42:08 GMT

Create the stored process without using the stored process macros, as those occupy the _webout destination.

Configure your STP as stream output.

Use _webout as file reference for your CSV output file.

To make it easier for the browser to handle the content, you can add (data _null_ step with file _webout;) the necessary HTML code that identifies the content type. HTML knowledge needed.

Alternatively:

If you have a webserver running where SAS resides, you can store the CSV in a location that is within the DocumentRoot of the webserver; the STP should then create a simple webpage containing the URL of the file. That way, the webserver will automatically flag the content type.

Re: Export excel using stored process

RW9 — Tue, 27 Sep 2016 08:45:37 GMT

I would sort out the base code first then before worrying about the download. Why does the file say corrupt? Run that part on your local machine, make sure to apply a style and correct output. I have got that sometimes where my proc report doesn't have a columns statement (although that might just be my setup). Not sure what bit in that code is what your talking about, the commented out section:

/*ods tagsets.excelxp file= "c:\report.xml";
Proc tabulate data=GL_Claims.xml;
run;
ods tagsets.excelxp close;*/

Doesn't seem right (unless you have a dataset called XML). And the other, the proc print doesn't seem to have any ods statements associated with it - this may just be that system I do not know. Normally to produce a file I would see:

ods tagsets.excelxp file="...\file.xml" style=statistical options(sheet_name="Sheet");
proc report data=your_data nowd split="*";  
  columns _all_;
  define col1 / "Hello";
  define col2 / "World";
run;
ods tagsets.excelxp close;

Note that Excel does qeustion the opening of some filetypes, but that doesn't mean the file is corrupted.

Re: Export excel using stored process

Vince_SAS — Tue, 27 Sep 2016 12:27:16 GMT

Without having more details, my guess is that you are getting a message like this when you try to open the file:

The file that you are trying to open, 'GL_Claims.xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?

Try changing the extension in your STPSRV_HEADER call to .xml to match the content of the file:

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

Vince DelGobbo

SAS R&D

Re: Export excel using stored process

GeorgeGiannako — Tue, 27 Sep 2016 12:45:39 GMT

well this is exactly what it says trying to open the excel document. i cant thank everybody enough for their timely responses. i have tried almost everything people have suggested in this post. this was the last piece of code i used (still getting same error):

*ProcessBody;
data _null_;

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

%let _ODSDEST=TAGSETS.MSOFFICE2K;    
%let _ODSSTYLE=seaside;

/* Create output */
%stpbegin;

  proc print data=VA_WORK.WRK_TEST_GL_CLAIMS;
  run;

%stpend;


*  Begin EG generated code (do not edit this line);
;*';*";*/;quit;
%STPEND;

*  End EG generated code (do not edit this line);

I believed this would be so much easier

Re: Export excel using stored process

Vince_SAS — Tue, 27 Sep 2016 12:50:39 GMT

The tagsets.ExcelXP destination creates XML content so you need to use .xml in the STPSRV_HEADER call.

The tagsets.MSOffice2K destination creates HTML content so you need to use .htm in the STPSRV_HEADER call.

This is a Microsoft limitation; they require the extension to match the content of the file.

Vince DelGobbo

SAS R&D

Re: Export excel using stored process

GeorgeGiannako — Tue, 27 Sep 2016 13:27:12 GMT

My gosh, i changed to htm using tagsets.MSOffice2K and it worked! Now I only need to change to actual excel output (and maybe change the font, because the greek alphabet appears gibberish).