Your SAS programs, embedded in web apps and elsewhere

_webout with Proc Export?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

_webout with Proc Export?

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?


Accepted Solutions
Solution
3 weeks ago
PROC Star
Posts: 1,271

Re: _webout with Proc Export?

[ Edited ]

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;

View solution in original post


All Replies
Super User
Posts: 3,188

Re: _webout with Proc Export?

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.

SAS Employee
Posts: 295

Re: _webout with Proc Export?

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

 

Vince DelGobbo

SAS R&D

 

Occasional Contributor
Posts: 13

Re: _webout with Proc Export?

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

SAS Employee
Posts: 295

Re: _webout with Proc Export?

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

 

Solution
3 weeks ago
PROC Star
Posts: 1,271

Re: _webout with Proc Export?

[ Edited ]

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;
Occasional Contributor
Posts: 13

Re: _webout with Proc Export?

Thanks. It worked fantastically for me.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 153 views
  • 0 likes
  • 4 in conversation