BookmarkSubscribeRSS Feed
jklein271
Calcite | Level 5
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
2 REPLIES 2
Doc_Duke
Rhodochrosite | Level 12
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
Calcite | Level 5
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.

sas-innovate-2024.png

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.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1211 views
  • 0 likes
  • 2 in conversation