BookmarkSubscribeRSS Feed
trich12
Calcite | Level 5

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!

2 REPLIES 2
Doc_Duke
Rhodochrosite | Level 12

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

PGStats
Opal | Level 21

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

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

PG

PG

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 2 replies
  • 9247 views
  • 0 likes
  • 3 in conversation