BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mrg1212005
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Quentin
Super User

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;
BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

View solution in original post

6 REPLIES 6
SASKiwi
PROC Star

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
Rhodochrosite | Level 12

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
Calcite | Level 5

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

Vince_SAS
Rhodochrosite | Level 12

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
Super User

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;
BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
mrg1212005
Calcite | Level 5
Thanks. It worked fantastically for me.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2898 views
  • 2 likes
  • 4 in conversation