CODEWITHSUNDEEP
sas
EvilBob22
EvilBob22

Reputation: 732

SAS PROC PRINT is really slow for me, any ideas?

Let me start by saying that I'm on a team that are all very new to SAS. We are using Enterprise Guide 5.1 in SAS 9.3, and have a set of schedule data arranged vertically (one or two rows per person per day). We have some PROC SQL statements, a PROC TRANSPOSE, and a couple other steps that together primarily make the data grouped by week and displayed horizontally. That set of code works fine. The first time the process flow runs, it takes a little extra time establishing the connection to the database, but once the connection is made, the rest of the process only takes a few seconds (about 6 seconds for a test run of 7 months of data: 58,000 rows and 26 columns of source data going to 6,000 rows, 53 columns of output).

Our problem is in the output. The end-users are looking for results in Excel, so we are using the SAS Excel add-in and opening a stored process. In order to get output, we need a PROC PRINT, or something similar. But using PROC PRINT on the results from above (6,000 rows x 53 columns) is taking 36 seconds just to generate. Then, it is taking another 10 seconds or so to render in EG, and even more time in Excel.

The code is very basic, just:

PROC PRINT DATA=WORK.Report_1
    NOOBS
    LABEL;
RUN;

We have also tried using a basic PROC REPORT, but we are only gaining 3 seconds: it is still taking 33 seconds to generate plus rendering time.

PROC REPORT DATA=WORK.Report_1
    RUN;
QUIT;

Any ideas why it is taking so long? Are there other print options that might be faster?

Upvotes: 2

Views: 3573

Answers (1)

DomPazz
DomPazz

Reputation: 12465

Tested on my laptop. Took about 13 seconds to output a table with 6000 records and 53 variables (I used 8 character long strings) with PROC PRINT and ODS HTML. 

data test;
format vars1-vars53 $8.;
array vars[53];
do i=1 to 6000;
    do j=1 to 53;
        vars[j] = "aasdfjkl;";
    end;
    output;
end;    
drop i j;
run;

ods html body="c:\temp\test.html";
proc print data=test noobs;
run;
ods html close;

File size was a little less than 11M.

If you are only using this as a stored process, you can make it a streaming process and write to _WEBOUT HTML. This will work for viewing in Excel and greatly reduces the size of the HTML generated (no CSS included).

data _null_;
set test end=last;
file _webout;
array vars[53] $;
format outstr $32.;
if _n_ = 1 then do;
    put '<html><body><table>';
    put '<tr>';
    do i=1 to 53;
        outstr = vname(vars[i]);
        put '<th>' outstr '</th>';
    end;
    put '</tr>';
end;

put '<tr>';
do i=1 to 53;
    put '<td>' vars[i] '</td>';
end;
put '</tr>';

if last then do;
    put '</table></body></html>';
end;
run;

This takes .2 seconds to run and generated 6M of output. Add any HTML decorators as needed.

Upvotes: 2

Related Questions