Help using Base SAS procedures

sort execution failure

Posts: 23

sort execution failure

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);


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!

Trusted Advisor
Posts: 2,125

Re: sort execution failure

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

Esteemed Advisor
Posts: 5,540

Re: sort execution failure

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

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


Ask a Question
Discussion stats
  • 2 replies
  • 3 in conversation