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

[jklein271]

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

[Doc_Duke]

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.

[jklein271]

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.