How do I export sas dataset to csv using stored process ??

[nbasnet]

I'm using SAS Stored Processes to generate reports in a web browser. I created an export button so that the user can export the data in csv format. 

Here is the code:

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 report data=xyz;
run;

ods csv  close;

The csv is generated, but with all the html code of the current page included. Below is the screenshot of the output. Is there an option in ods csv to export just the data? thanks

[Tom]

I don't think the ODS CSV is the issue.  But you can easily test by generating the CSV file using a data step instead.  

The simpliest is to generate the data without a header line. Then you can test and if the extra HTML content is there it is coming from the share process execution and not from the actual SAS program.  If that works it is not hard to make a data step that also generates the header line. 

data _null_;
rc = stpsrv_header('Content-type','application/vnd.ms-excel');
rc = stpsrv_header('Content-disposition','attachment; filename=temp.csv');
run;
data _null_;
  file _webout dsd ;
  set xyz ;
  put (_all_) (+0);
run;

[boemskats]

What @Tom suggests is the correct approach, and should prompt your end user to download a .csv file. I'd code it slightly differently, so I'm including the following code in case the variation helps you understand what's going on.

/* filename to write temp file to if you want an intermediate file for whatever reason */
filename mygenfn temp;

/* then insert code here to write your csv to that tem filename above */

/* set the headers same as @Tom did */
%let RV=%sysfunc(stpsrv_header(Content-type,application/vnd.ms-excel));
%let RV=%sysfunc(stpsrv_header(Content-disposition,attachment; filename=something.csv));

/* bytestream out to _webout for the browser to download */ 
data getout;
  length data $1;
  infile mygenfn recfm=n;
  file _webout recfm=n mod;
  input data $char1. @@;
  put data $char1. @@;
run;

I've also used variations on the above data step, including put _infile_ instead of put data $char1. @@. Same idea. This post might also give you something to think about depending on what you're trying to do. It's where I copied my answer above from.

Nik

[teelov]

Perfect!

[nbasnet]

Hi Tom, 

Thank you for your reply. I tried generating the data without the header line, and no HTML content was generated. But how do I make a data step that also generates with the header line ?

[Tom]

There is an example of generating a CSV file with headers using a single data step posted by data_null_ in this thread.

https://communities.sas.com/t5/Base-SAS-Programming/Output-to-delimited-format/m-p/292767/highlight/...

A longer, but perhaps easier to understand method is to write the header line separately.  I am not sure how that translate into using the _WEBOUT fileref.  Do you need to use the MOD option on the FILE statement?

Here is method using multiple simple steps to write the header and then the data.

proc transpose data=HAVE(obs=0) ;
var _all_;
run;
%let names=&syslast;
data _null_;
  set &names end=eof;
  file _webout dsd ;
  put _name_ @;
  if eof then put;
run;
data _null_;
  set HAVE ;
  file _webout dsd MOD ;
  put (_all_) (+0);
run;
proc delete data=&names; run;

[boemskats]

@Tom

I'm not 100% sure if it works the same for file downloads (whether an EOF gets written), but in my experience you can comfortably use the _WEBOUT target with multiple datasteps in the same program without having to worry about keeping the stream open.

Nik

[Vince_SAS]

Is that the only code in your stored process?  It looks like the HTML destination is open and that ODS is writing HTML output to _WEBOUT.  This could happen if your code includes calls to the STPBEGIN/END macros.

Vince DelGobbo

SAS R&D