ERROR: Sort execution failure.

Reply
Frequent Contributor
Posts: 101

ERROR: Sort execution failure.

Hi,

Currently I am using SAS Data Integrator Studio......I have created a simple SAS job.

Just I am joining two tables with around 22 lakh records each by using SQL join transformation, but I am getting the following error :


ERROR: Sort execution failure.

I have searched this error in google...and used these cmds

  proc options group=memory; run;

  proc options option=utilloc; run;

  proc options option=threads; run;

  options fullstimer msglevel=i ;

  proc options option=workperms; run;

But still I am getting the same error.And in some site, they asked to incresease the sortsize and memsize....

Where to increase these size?

Kindly help.......Thanks in advance!!!!!!!

Super User
Posts: 7,764

Re: ERROR: Sort execution failure.

Posted in reply to Helannivas

You may run out of disk space in your UTILLOC. If UTILLOC is not specified in your sasv9.cfg, then look at the free space in your WORK location.

Also consider to join the tables by using PROC SORT individually on each table and then do a merge in a DATA step; this is usually less intensive in disk space requirements.

You may also have a problem with permissions, see

39705 - Troubleshooting Sort Initialization and Sort Execution Failure errors in SQL procedures in W...

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 101

Re: ERROR: Sort execution failure.

Posted in reply to KurtBremser

Thanks KurtBremser for your reply....

But in sasv9 config file....I have this message

" Do NOT modify this file.  Any additions or changes should be made in sasv9_usermods.cfg."

Can I edit in sasv9_usermods.cfg file?  And Can I use the below cmd in my config file

"sas -memsize 1G -sortsize 512M " . Please help.

Super User
Posts: 7,764

Re: ERROR: Sort execution failure.

Posted in reply to Helannivas

Yes, you can and should put your modifications in the sasv9_usermods.cfg, as that will not be overwritten if you apply a hotfix or similar.

Be sure to identify exactly which .cfg is the one for the server that DI Studio is using, there are several in your configuration tree.

Do not enter "sas -memsize 1G -sortsize 512M" in your config file, because that would be the complete commandline. Instead enter "-memsize 1G" and "-sortsize 512M" (without the quotes) on separate lines in the sasv9_usermods.cfg file.

If you still have problems, take a look at where the WORK library is created (SAS creates a new directory dynamically during startup and deletes it with all its contents at regular shutdown). You may run out of disk space there. If your tables are larger than your sortsize (which will happen sooner or later), SAS will need to create a utility file, and you always have a utility file with SQL.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 3,212

Re: ERROR: Sort execution failure.

Posted in reply to Helannivas

As you are using DI there is a server installation (BI/DI).

That whole thing is having a metadata-server and more services.

Do not change the config or usermod files at the Foundation tree (installation).

These are system wide and should thought of settings related to the whole (virtual) machine.

For each appserver (SASApp) there is configuration tree with all kind of configuration-files

The structure has been a logical construct of nesting on autoexec's config's and scritpting. The implementation errors by SAS-inst can be ignored in your case.

The best place for adjustment of a user related config-file is the ../Levx/SASApp  config_usermod file.

If you uses this name SASApp that will be easy to be found otherwise use the name appserver name you are using instead.

Ask your platform admin to do that change (if that is no you) as it is part of the installation/configuration.

---->-- ja karman --<-----
New Contributor
Posts: 2

Re: ERROR: Sort execution failure.

Posted in reply to Helannivas

before editing any configuration files in SAS , Make sure that your work path/location having  required memory to run sort operations .

Super User
Posts: 3,252

Re: ERROR: Sort execution failure.

Posted in reply to Helannivas

What is your current MEMSIZE setting? With SAS 9.3 I have found that the SAS default insufficient, so increased it to 2GB. This value would need to be tested based on your own needs and environment though.

Also if you apply this change to your sasv9_usermods.cfg file in your SASApp folder, it will then apply to any SAS jobs running on this server, regardless of their type (batch, stored process, workspace etc)

Ask a Question
Discussion stats
  • 6 replies
  • 24234 views
  • 3 likes
  • 5 in conversation