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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.