topic Re: Insufficient space in file WORK.'SASTMP-000000024'n.UTILITY. in SAS Programming

Insufficient space in file WORK.'SASTMP-000000024'n.UTILITY.

Alexxxxxxx — Tue, 27 Apr 2021 07:50:59 GMT

Hello,

I am running the code and shows the following problem,

84   proc sql;
85       create table Process.merge4 as
86       select distinct
87       inpat.psn_name,
88       total_appln,
89       UPCASE(PERSON_CTRY_CODE) as PERSON_CTRY_CODE,
90       UPCASE(PERSON_ADDRESS) as PERSON_ADDRESS
91       from Process.merge3_1 as inpat
92       left join Pat2020.tls206_PERSON (keep=psn_name PERSON_CTRY_CODE PERSON_ADDRESS) as inco on
92 ! inpat.psn_name=inco.psn_name
93       /*where person_ctry_code ne ''*/
94       order by inpat.psn_name
95       ;
ERROR: Insufficient space in file WORK.'SASTMP-000000024'n.UTILITY.
ERROR: File WORK.'SASTMP-000000024'n.UTILITY is damaged. I/O processing did not complete.
NOTE: Error was encountered during utility-file processing. You may be able to execute the SQL
      statement successfully if you allocate more space to the WORK library.
ERROR: There is not enough WORK disk space to store the results of an internal sorting phase.
ERROR: An error has occurred.

96   quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           8:55:48.21
      cpu time            9:36.09

I have used the

libname user 'H:\SAS_work';

to set a larger space for work library (2.59T free of H:\).

and used

%put %sysfunc(pathname(work));

to 'Clean out old SAS Work Folders'.

But still, get this result.

Could you please give me some advice about this?

Thanks in advance.

Re: Insufficient space in file WORK.'SASTMP-000000024'n.UTILITY.

Kurt_Bremser — Tue, 27 Apr 2021 07:59:41 GMT

Several points

don't use a networked drive for WORK. WORK should always be on the fastest storage you have available, which either means local or SAN. H: let's me assume this is a network share
Maxim 3: Know Your Data. How many observations are in there, what are the column (variable) attributes?
Most important: the frequencies of psn_name. If you have 1000 "John Miller" in both datasets, you'll get a million "John Miller" observations in the utility file before the DISTINCT is applied

Re: Insufficient space in file WORK.'SASTMP-000000024'n.UTILITY.

Alexxxxxxx — Tue, 27 Apr 2021 08:14:04 GMT

Hello @Kurt_Bremser ,

many thanks for your reply.

1) H:\ is an external hard drive. is that OK?

2) Process.Merge3_1: 3 variables * 2127026 obs.

Pat2020.Tls206_person: 7 variables * 77596670 obs ( but I only keep 3 variables in proc SQL).

3) Do you have some suggestion for this point? what should I do for a high frequent psn_name?

Re: Insufficient space in file WORK.'SASTMP-000000024'n.UTILITY.

Kurt_Bremser — Tue, 27 Apr 2021 08:41:16 GMT

If you get large cartesian joins, you need to find a way to make the join restrictive, e.g. join additionally on zipcodes or birth dates or ....

After all, the result in its current form would be massively inaccurate, from what I get about your data.

Re: Insufficient space in file WORK.'SASTMP-000000024'n.UTILITY.

Kurt_Bremser — Tue, 27 Apr 2021 08:45:52 GMT

If, by "external" you mean a USB drive, that will still be slower than a SATA internal disk. If you use a personal workstation, look for a good SATA SSD as a WORK drive. This is because the WORK (and UTILLOC) location has to do a lot of random disk accesses, while permanent libraries are used mostly to sequentially read and write datasets.

Re: Insufficient space in file WORK.'SASTMP-000000024'n.UTILITY.

Patrick — Tue, 27 Apr 2021 09:34:47 GMT

The temporary sort files get created under utilloc. What do you get when running below two commands? Are they pointing to your disk with a lot of free space available?

%put work:    %sysfunc(getoption(work));
%put utilloc: %sysfunc(getoption(utilloc));

Re: Insufficient space in file WORK.'SASTMP-000000024'n.UTILITY.

Alexxxxxxx — Tue, 27 Apr 2021 09:56:19 GMT

hello @Patrick ,

many thanks for your reply.

1    %put work:    %sysfunc(getoption(work));
work:    C:\Users\70660\AppData\Local\Temp\SAS Temporary Files\_TD14308_DESKTOP-EF53L4G_
2    %put utilloc: %sysfunc(getoption(utilloc));
utilloc: WORK

I do not have a lot of free space available in C:\. Is there any method I can use to change temporary files or utilloc to a disk with a lot of free space available?

Re: Insufficient space in file WORK.'SASTMP-000000024'n.UTILITY.

Alexxxxxxx — Tue, 27 Apr 2021 09:59:01 GMT

thanks @Kurt_Bremser

is there any method to change 'WORK (and UTILLOC) location' to the other disk which has a large free space?

Re: Insufficient space in file WORK.'SASTMP-000000024'n.UTILITY.

Kurt_Bremser — Tue, 27 Apr 2021 11:04:00 GMT

The WORK location is set at SAS start from the configuration or commandline.

Follow this Knowledge Base article: https://support.sas.com/kb/19/247.html

Re: Insufficient space in file WORK.'SASTMP-000000024'n.UTILITY.

Ksharp — Tue, 27 Apr 2021 12:24:41 GMT

Try batch process :
Make a run.bat which contains the following and double click run.bat

"D:\SASHome\SASFoundation\9.4\sas.exe" -nosplash -sysin "c:\temp\temp.sas" -log "c:\temp\temp#Y#m#d-#H-#M-#s.log" -work "c:\temp\"

Re: Insufficient space in file WORK.'SASTMP-000000024'n.UTILITY.

Patrick — Tue, 27 Apr 2021 21:07:26 GMT

@Alexxxxxxx wrote:

hello @Patrick ,

many thanks for your reply.
1    %put work:    %sysfunc(getoption(work));
work:    C:\Users\70660\AppData\Local\Temp\SAS Temporary Files\_TD14308_DESKTOP-EF53L4G_
2    %put utilloc: %sysfunc(getoption(utilloc));
utilloc: WORK
I do not have a lot of free space available in C:\. Is there any method I can use to change temporary files or utilloc to a disk with a lot of free space available?

Ideally create the required space on your C drive by deleting "garbage" or by moving content to your H drive. For cleaning up running WinDirStats (link provided in previous post) normally gives you a good idea where there could be "garbage" - not the least left over SAS files from corrupted SAS sessions.

The path for WORK and UTILLOC get assigned during SAS invocation and you can't change them anymore out of a running SAS session. You can of course change paths permanently in the .cfg but be aware that if your H drive then is once not available SAS will not work and that if pointing to a slower disk then SAS performance will deteriorate.

For a "one off" running stuff in batch as @Ksharp suggests is eventually your way to go (if you really can't free up space on C) as here you can explicitly define any parameter - i.e. -work "H:\SAS_work"

Re: Insufficient space in file WORK.'SASTMP-000000024'n.UTILITY.

ChrisNZ — Tue, 27 Apr 2021 21:26:22 GMT

Utility files do no go to the WORK library. They go to the UTILLOC path. See here

As @Kurt_Bremser said, putting this path or the WORK path on a slow drive might allow for more space, but will kill your speed.

Re: Insufficient space in file WORK.'SASTMP-000000024'n.UTILITY.

ChrisNZ — Tue, 27 Apr 2021 21:30:58 GMT

> Is there any method I can use to change temporary files or utilloc to a disk with a lot of free space available?

You can do that by changing the config file, or adding invocation options as shown:

C:\sas\sas.exe -work=c:\temp -utilloc=c:\temp

See here