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
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.
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.