HELP: Write to WORK.'#tf0174'n.UTILITY failed..?

[annka]

Hello,

I'm getting this weird error. My program maps two big tables. One serves as a mapping table and the other is the table, whose columns i need to replace. But in the middle of it, the program suddenly gives me this weird error. I never accessed .utility anywhere in my program. Below is the log. Any idea why it's suddenly doing that? I already checked my directory.. It's not full yet.

Thanks

Regards,
Annka

NOTE: There were 180 observations read from the data set LIBR.MAPPING_TBL.
NOTE: There were 116 observations read from the data set CHILD.DATE_TBL.
NOTE: There were 180 observations read from the data set WORK.DUMMY1.
NOTE: The data set WORK.DUM2 has observations and 98 variables.
NOTE: Compressing data set WORK.DUM2 decreased size by 69.01 percent.
59 The SAS System 17:02 Tuesday, January 1, 2008

Compressed is 1628 pages; un-compressed would require 5313 pages.
NOTE: DATA statement used:
real time 23.26 seconds
cpu time 23.17 seconds



ERROR: Write to WORK.'#tf0174'n.UTILITY failed. File is full and may be damaged.
ERROR: Write to WORK.'#tf0174'n.UTILITY failed. File is full and may be damaged.
ERROR: Write to WORK.'#tf0174'n.UTILIT
NOTE: The SAS System stopped processing this step because of errors.
NOTE: SAS set option OBS=0 and will continue to check statements. This may cause NOTE: No observations in data set.
NOTE: There were 113 observations read from the data set CHILD.DATE_TBL.
NOTE: PROCEDURE SORT used:
real time 1:15.80
cpu time 13.24 seconds


ERROR: SAS ended due to errors.

 

[Sastronaut]

.utility is normally a file created when using proc sql...are you using proc sql to process or access data?

 

[annka]

im not using proc sql in the part where that error is generated. It is a proc sort statement on a very large dataset (I replaced the no. of obs above). I read from the sas site that it could be that my sort size is not enough.
Is it possible to reassign my sort space? I am using sas8 on unix.

 

[Sastronaut]

First i'd take a look at your sort statement and input data set. How many records are being sorted and how many by variables are you using? If you can drop variables from the output data set then drop them on the data= statement. Otherwise one thing you can try is the tagsort option.

proc sort data=yourdata TAGSORT;
    by byvar1 byvar2...;
end;

If that doesn't work try to break the data into segments and sort independently then set back together.

proc sort data=yourdata TAGSORT out=sort1;
    where byvar1 <='M';
    by byvar1 byvar2 ...;
proc sort data=yourdata TAGSORT out=sort2;
    where byvar > 'M';
    by byvar1 byvar2 ...;
run;

data yourdatasorted;
    set sort1
        sort2;
    by byvar1 byvar2;
run;

a final option i'd try is to adjust your sort size to max.

proc sort data=yourdata SORTSIZE=MAX out=yourdatasorted;
    by byvar1 byvar2 ...;
run;

 

[ChrisW75]

Sometimes this happens when the disk runs out of space, sorting a file can use ALOT of disk space. It's caught me out a few times.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[annka]

thanks! im trying to break the data into smaller segments. the tagsort did not work.

 

[ChrisW75]

Ick. Tagsort sucks. Don't use it.

It looks kind of like your SAS dataset started life as multiple datasets which youbrought together, then sorted. One thing you can do it sort each dataset individually, then SET them together in a datastep with a BY statement to retain the sort order.

proc sort data=dset1;
  by key;
run;

proc sort data=dset2;
  by key;
run;

data dset_all;
  set dset1
      dset2;
  by key;
run;

It can make quite a difference.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[Sastronaut]

chris - just out of curiousity why do you think tagsort doesn't work well?

 

[ChrisW75]

Performance wise it's REALLY slow. We had a guy here who used it exclusively all the time for every sort he did. Someone else was using his code (he's since left) recently and asked me to help as it was taking for ever. I cut out the TAGSORT option and it took seconds to run.
I guess it may be useful in certain circumstances, perhaps where there is really limited disk space (which I guess is the issue here, so sorry, TAGSORT might be right here), but for the most part I'd say it's worth avoiding.

Also, SORTSIZE=MAX I think sets the sortsize to the max allowed, but this in turn is a number set in the system options at startup (I had a play around with these options about a year ago when I was having problems with sorting some big files), so it might not improve much, there's a whole bunch of options aroundthe amount of memory SORT can used which it's worth playing with to get the best performance.


Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.