topic Re: User Defined Formats Issue in EG 4.2 (ERROR: Sort execution failure.) in SAS Enterprise Guide

User Defined Formats Issue in EG 4.2 (ERROR: Sort execution failure.)

jklein271 — Tue, 20 Jul 2010 13:36:56 GMT

It runs fine when not using custom formats in the query:

proc sql;
create table universe_national as
select Month,
/* PUT(zipcode,$fzipcn.) as Region_Code,*/
/* PUT(zipcode,$fzipcn.) as Region_Name,*/
SUM(universe) as Universe
from UNIVERSE.amicount
where Month GE '01JAN2000'd and Month LE &current.
group by Month/*, Region_Code, Region_Name*/;
quit;

NOTE: PROCEDURE SQL used (Total process time):
real time 38.33 seconds
user cpu time 48.38 seconds
system cpu time 2.96 seconds

However, it takes 30 minutes to execute and bombs if they are included in the select statement and then in the group by statement:

proc sql;
create table universe_national as
select Month,
PUT(zipcode,$fzipcn.) as Region_Code,
PUT(zipcode,$fzipcn.) as Region_Name,
SUM(universe) as Universe
from UNIVERSE.amicount
where Month GE '01JAN2000'd and Month LE &current.
group by Month, Region_Code, Region_Name;
quit;

ERROR: Sort execution failure.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 26:07.29
user cpu time 35:52.91
system cpu time 10:54.76

Re: User Defined Formats Issue in EG 4.2 (ERROR: Sort execution failure.)

Doc_Duke — Tue, 20 Jul 2010 15:37:23 GMT

This is probably an issue with the SAS Server and not with EGuide. If you search the SAS Support site for
SQL User Defined Formats
you will see lots of weird problems that occur with different specific SAS Server configurations. Probably best to contact tech support directly.

Re: User Defined Formats Issue in EG 4.2 (ERROR: Sort execution failure.)

jklein271 — Wed, 21 Jul 2010 12:09:53 GMT

Thanks, Doc. It ended up just being a 32 bit vs 64 bit issue for a few random formats sitting in a smaller catalog. I was able to fix it using proc format cntlout / cntlin. I'm glad I figured it out before tech support.