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.
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
Several points
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?
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.
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.
thanks @Kurt_Bremser
is there any method to change 'WORK (and UTILLOC) location' to the other disk which has a large free space?
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
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));
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?
@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: WORKI 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"
> 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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: