topic How to have a SAS Stored Process return a csv in SAS Programming

How to have a SAS Stored Process return a csv

UdayGuntupalli — Sun, 14 Oct 2018 02:58:37 GMT

All,

I have a stored process that I have developed and it is able to return an XML when invoked using a SOAP call. However, in the response, SAS automatically encodes the response and returns "<" as "&lt".
I would like to request to request your help for :

1. Is there a way to ensure that SAS response does not have this encoded ? (Please note that, I have already tried reading the XML response as a string and using tranwrd function to replace the "<" and also using htmldecode function. However, in both the cases, the "&lt" still remains

2. Is there a way to respond with a '.csv' file when the SAS stored process is called ? (Kindly provide an example)

Appreciate your help

Best
uday

Re: How to have a SAS Stored Process return a csv

Cynthia_sas — Sun, 14 Oct 2018 13:58:23 GMT

Hi:
Yes, there is a way to have your stored process return a CSV file. However, you typically need to change the content-type header and the ODS destination BEFORE your %STPBEGIN call. Are you using %STPBEGIN/%STPEND around your main stored process code?

Look at the CSV example (the last example) on this Tech Support note: http://support.sas.com/kb/32/634.html

Cynthia

Re: How to have a SAS Stored Process return a csv

UdayGuntupalli — Sun, 14 Oct 2018 16:46:25 GMT

@Cynthia_sas,
Thank you. In the example

/* Sample for downloading a CSV file. */

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

ods csv file=_webout;
                                                                                                                                                                                                       
proc print data=sashelp.class; 
run;

ods csv close;

In the above example, let us assume my stored process's output is the Class data set. Should it be saved as a file ? Which part of the above code actually takes the data set and exports as csv ? The data step does not identify the data source for the file to be exported while the print step would only print to the log, so I am kind of confused as to which step is actually specifying the data source for the response.

Best

Uday

Re: How to have a SAS Stored Process return a csv

Tom — Sun, 14 Oct 2018 17:34:46 GMT

@UdayGuntupalli wrote:

@Cynthia_sas,
Thank you. In the example
/* Sample for downloading a CSV file. */

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

ods csv file=_webout;
                                                                                                                                                                                                       
proc print data=sashelp.class; 
run;

ods csv close;
In the above example, let us assume my stored process's output is the Class data set. Should it be saved as a file ? Which part of the above code actually takes the data set and exports as csv ? The data step does not identify the data source for the file to be exported while the print step would only print to the log, so I am kind of confused as to which step is actually specifying the data source for the response.

Best

Uday

That code is use the CSV ods destination to write to the _WEBOUT fileref. So the PROC PRINT results will be converted to CSV format and written to _WEBOUT. If the rest of the Stored Process setup is correct the whatever is written to _WEBOUT is what is returned to the caller.

Re: How to have a SAS Stored Process return a csv

Cynthia_sas — Sun, 14 Oct 2018 19:48:21 GMT

Hi:
The PROC PRINT does NOT only print to the LOG. The ODS CSV destination is writing to "_webout", which is the stored process "pipeline" from the requesting browser or client application to the application server.

Notice in the Stpsrv_header function calls the fact that the requesting browser should open vnd.ms-excel when the stored process results come back down the _webout "pipeline".

Also, there is a content-disposition change to indicate a preference for the filename to be called "temp.csv" -- however, in my experience sometimes, this name is NOT used when the _webout stream is opened.

Note that you have to submit the stored process from a client application that would accept the results. For example, this is not a stored process that could be invoked from the SAS Add-in for Microsoft Office, such as Word or PowerPoint. However, it could be invoked from the SAS Portal or the Stored Process Web Application.

In the above example, the stored process output is NOT the class data set. It will be the Proc Report data, as output by PROC PRINT, written as comma separated value files. It will NOT be the same as an exported file from PROC EXPORT.

For example -- consider these 2 output files from non-stored process code -- submitted in a SAS session:

The difference is that when you use ODS CSV and _webout, the results are "streamed" back to the requesting client application, which is usually a browser and which uses the content-type header to determine how the requesting client application will receive the output stream from the stored process.

Cynthia

Re: How to have a SAS Stored Process return a csv

UdayGuntupalli — Mon, 15 Oct 2018 14:39:12 GMT

@Cynthia_sas & @Tom,
While creating the stored process, I definitely ensured that "Stored Process Macros" was unchecked under the Include Code for option. However, I receive this error both for my own stored process and another test one we created for exporting "Class" dataset.

Re: How to have a SAS Stored Process return a csv

Cynthia_sas — Mon, 15 Oct 2018 14:43:00 GMT

Hi:
That's not enough of the log to tell anything. I'd recommend working with Tech Support. How you created, registered and run the stored process makes a difference. For example, what was your client application? Did you use the Stored Process Web App? The message about no logical assign for filename _WEBOUT usually means that you registered the stored process incorrectly and did not indicate that it would produce streaming results, as described here: http://support.sas.com/kb/34/120.html

That's why I recommend working with Tech Support. Posting 2 lines of Log isn't going to give anyone the true picture of how you defined and ran the stored process.

Cynthia