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!
It appears you are running out of memory because you are asking SQL to do a Cartesian product.
1) Are the datasets _temp and _caldates already sorted by evtdate?
2) Which evtdate are you trying to drop from the result?
PG
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.