sort execution failure

trich12 — Tue, 28 May 2013 20:00:03 GMT

I've been trying to run a proc sql and get a sort execution failure every time. Here is the code:

proc sql;

create table _temp (drop=evtdate)

as select a.*, a.evtdate as _edate format date9., b.*

from _temp a left join _caldates (drop=time) b

on b.evtdate-a.evtdate>=0

group by a.evtdate

having (b.evtdate-a.evtdate)=min(b.evtdate-a.evtdate);

quit;

And the error message:

NOTE: SAS threaded sort was used.

ERROR: Sort execution failure.

1247 quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 1:08:58.65

user cpu time 2:08.34

system cpu time 11:55.76

Memory 68343k

OS Memory 78456k

Timestamp 5/22/2013 12:20:47 PM

The dataset _temp has 9468 observations and 222 variables. The dataset _caldates has 9351 observations and 6 variables. I would not expect these datasets to be too large to cause this problem from what I've seen about the sort execution failure. Any help would be so appreciated! Thanks!

Re: sort execution failure

Doc_Duke — Tue, 28 May 2013 20:50:43 GMT

It appears you are running out of memory because you are asking SQL to do a Cartesian product.

Re: sort execution failure

PGStats — Tue, 28 May 2013 21:52:15 GMT

1) Are the datasets _temp and _caldates already sorted by evtdate?

2) Which evtdate are you trying to drop from the result?

topic sort execution failure in SAS Procedures

sort execution failure

Re: sort execution failure

Re: sort execution failure