_webout with Proc Export?

[mrg1212005]

I have a stored process which creates excel extracts and let the user downloads on thier system. Currently, I am using ODS excel with _webout to create spreadhseets having multiple tabs. This method works fine when there are a couple of thousand of records but perform poorly for larger extracts.

I cannot use ODS CSV as it dont creates multiple tabs spreadshhet. I dont want to use ODS tagest.excelxp as it dont creates native excel file and physical file would be huge for 100K spreadhseet.

Someone suggested me that using Proc EXPORT would be more effieicnt that above techniques. but I am not sure if I can use that with _webout. 

I am wondering if anyone has used Proc Export with _webout or can suggest better aproach to create excel file which is multi-tabs and let business user download extract to thier system using streaming output?

[SASKiwi]

PROC EXPORT or the LIBNAME EXCEL or XLSX engines would be worth exploring. SAS/ACCESS for PC File Formats would need to be installed and licensed for these to work.

[Vince_SAS]

What version of SAS are you using, and do you license the SAS/ACCESS to PC File Formats product?

Vince DelGobbo

SAS R&D

[mrg1212005]

Vince- I am using SAS v9.4_M4. I also have SAS/ACCESS to PC files,

[Vince_SAS]

Some performance issues with the ODS Excel destination were corrected in SAS 9.4 M4, and more changes were made in the upcoming M5 release.  As a workaround, you can start the SAS Server with a larger value for the MEMSIZE option as mentioned in this SAS Note:

Problem Note 56985: An out of memory condition might occur when you use the ODS destination for Excel with large tables

http://support.sas.com/kb/56/985.html

A larger value of MEMSIZE helps with out-of-memory conditions but likely won't do much for overall execution time.

PROC EXPORT or the one of the LIBNAME engines may be an alternative for you.  Workbooks created using the SAS/ACCESS to PC FIles Interface contain only the data, and no markup.  In other words, you don't get the rich formatting that the ODS destinations provide.  Is that acceptable to you?

Vince DelGobbo

SAS R&D

[Quentin · Accepted solution]

For large files, I've found PROC EXPORT to be much faster than just about any ODS destination combined with PROC PRINT or PROC REPORT. 

As Vince says, you don't get the pretty formatting with PROC EXPORT, but when you just want a way for users to pull "raw" data into Excel, it works fine. 

I typically write the Excel file to some temp location, then (after some automated log checking), stream it back to the _webout with  a data _null_ step.  Something like:

  proc export data=MyData
              outfile="%sysfunc(pathname(work))/somefile.xlsx" 
              dbms=xlsx
              label 
              replace
  ;
 run;


  *Binary copy code from http://support.sas.com/kb/6/588.html, IO think there are  ;
  *better ways to do this if you google a bit;

  data _null_;
    file _webout recfm=s;
    infile "%sysfunc(pathname(work))/somefile.xlsx" recfm=n;
    if _n_ = 1 then do;
      rc = stpsrv_header('Content-type','application/vnd.ms-excel');  
      rc = stpsrv_header('Content-disposition',"attachment; filename=MyExcel.xlsx");
    end;
    input c $char1.;
    put c $char1. @@;
  run;

[mrg1212005]

Thanks. It worked fantastically for me.