topic Re: Problem with PROC SORT in a large dataset in SAS Programming

Problem with PROC SORT in a large dataset

Phil_NZ — Thu, 18 Mar 2021 23:18:51 GMT

Hi all SAS Experts,

I have been struggling with a proc sort step in my SAS code, could you please help me to sort it out?

My code and error is

NOTE: There were 162871058 observations read from the data set WORK.EX_NON_TRADING.


61         proc sort data=ex_non_trading_;
62         by gviidkey datadate;
63         run;

ERROR: No disk space is available for the write operation.  Filename = 
       C:\Users\pnguyen\AppData\Roaming\SAS\EnterpriseGuide\EGTEMP\SEG-19596-3f184c1f\contents\SAS Temporary 
       Files\SAS_util000100004A74_it082760\ut4A74000005.utl.
ERROR: Failure while attempting to write page 1085 of sorted run 106.
ERROR: Failure while attempting to write page 304325 to utility file 1.
ERROR: Failure encountered while creating initial set of sorted runs.
ERROR: Failure encountered during external sort.
ERROR: Sort execution failure.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 145088137 observations read from the data set WORK.EX_NON_TRADING_.
WARNING: The data set WORK.EX_NON_TRADING_ may be incomplete.  When this step was stopped there were 0 observations and 26 
         variables.
WARNING: Data set WORK.EX_NON_TRADING_ was not replaced because this step was stopped.

So, you can see the file ex_non_trading has 162871058 obs but it was damaged and reduced to only 145088137 obs after proc sort.

I really need your help and appreciate any contribution.

Warmest regards,

Phil.

Re: Problem with PROC SORT in a large dataset

Reeza — Thu, 18 Mar 2021 23:37:20 GMT

Talk to your IT team or someone internal to your company because I suspect this is a process issue. You've previously stated you're on a server but it appears you're running it locally (C drive reference) so if you run it on the server instead you shouldn't have any issues. 162 million records is too big for a desktop machine. Not sure why/how you're running locally though.

Re: Problem with PROC SORT in a large dataset

Phil_NZ — Thu, 18 Mar 2021 23:44:13 GMT

Hi @Reeza

I did talk to the IT guys and he confirmed that I am running on a server rather than locally. I am quite surprised about that as well.

Warmest regards.

Re: Problem with PROC SORT in a large dataset

Reeza — Thu, 18 Mar 2021 23:54:29 GMT

Your log is stating otherwise....has your IT person seen that?

Anyways, you're a paid user, contact tech support if your IT team won't help you. This isn't something we can really help with. In general for sorting you need 3X the space of a data set, one to hold the main data set, a second to hold a temporary copy as it sorts and a third to hold the final version. Some algorithms don't work that way, but in general you're going to need a ton of space to sort a data set that size.

You can change where sorts happen to help with this, but again you may need to change your config file or use a user/temp library and that depends on your set up.

Re: Problem with PROC SORT in a large dataset

SASKiwi — Fri, 19 Mar 2021 01:25:31 GMT

If you want to know where your SAS is running in EG, in the Server List right click on the server name and select Properties. The Properties window will tell you the name of your SAS server. If you are running SAS locally on your PC then your server name is likely to be Local rather than SASApp.

Re: Problem with PROC SORT in a large dataset

ballardw — Fri, 19 Mar 2021 09:46:23 GMT

With large datasets try adding the TAGSORT option to the Proc Sort statement.

The TAGSORT option in the PROC SORT statement is useful in sorts when there might not be enough disk space to sort a large SAS data set. When you specify TAGSORT, the sort is a single-threaded sort. Do not specify TAGSORT if you want the SAS to use multiple threads to sort.

When you specify the TAGSORT option, only sort keys (that is, the variables specified in the BY statement) and the observation number for each observation are stored in the temporary files. The sort keys, together with the observation number, are referred to as tags. At the completion of the sorting process, the tags are used to retrieve the records from the input data set in sorted order. Thus, in cases where the total number of bytes of the sort keys is small compared with the length of the record, temporary disk use is reduced considerably. You should have enough disk space to hold another copy of the data (the output data set) or two copies of the tags, whichever is greater. Note that while using the TAGSORT option can reduce temporary disk use, the processing time can be much higher. However, on PCs with limited available disk space, the TAGSORT option can allow sorts to be performed in situations where they would otherwise not be possible.

Re: Problem with PROC SORT in a large dataset

FreelanceReinh — Fri, 19 Mar 2021 10:11:11 GMT

Hi @Phil_NZ,

I'm confident that using the TAGSORT option, as suggested by ballardw, will help because the error message suggests that a utility file got too big.

proc sort data=bigds tagsort;
by byvar1 ... byvarn;
run;

If even this reduction of the space requirements was not sufficient, I would check if more disk space could be made available by changing the value of the UTILLOC= system option (i.e., specify a different or an additional location).

If all else fails, you can still consider changes to the sort process (e.g., split the large dataset, ...).

Re: Problem with PROC SORT in a large dataset

Phil_NZ — Sat, 20 Mar 2021 22:24:03 GMT

Hi @SASKiwi

Sorry for replying late, I just got sick these days and just recover. Thank you for your suggestion and following your guidance, I have a Properties as below.

Does it mean that I am running locally rather than on a server?

Warm regards,

Re: Problem with PROC SORT in a large dataset

Phil_NZ — Sat, 20 Mar 2021 22:25:09 GMT

Hi @Reeza

Many thanks for your explanation and suggestion,@Reeza.

I learnt a lot about the mechanism behind the code from you.

Warmest regards,

Phil.

Re: Problem with PROC SORT in a large dataset

SASKiwi — Sat, 20 Mar 2021 22:35:09 GMT

@Phil_NZ - Correct. All of your SAS processing is on your PC. I suggest you open Windows Explorer on your PC and check the amount of free space you have on your local drives (C, D etc). That will tell you how big a SAS dataset you can cope with.

Re: Problem with PROC SORT in a large dataset

Phil_NZ — Sun, 21 Mar 2021 10:10:16 GMT

Hi @ballardw

Thank you for your insightful idea, the only problem of tagsort is that it takes more time to process comparing to proc sort without tagsort from my understanding. So, it seems to be a good idea that I let the computer run with tagsort at night when I am sleeping.

Warm regards.

Re: Problem with PROC SORT in a large dataset

Kurt_Bremser — Sun, 21 Mar 2021 12:49:53 GMT

TAGSORT is mainly used with large datasets that are stored with the COMPRESS option and a serious compression factor. Since the utility file will only contain the BY variables and the observation pointer, it is much smaller than the usual (uncompressed!) utility file that contains all columns.

Re: Problem with PROC SORT in a large dataset

mkeintz — Sun, 21 Mar 2021 19:53:41 GMT

You are using Compustat data, and you apparently created an identifier variable GVKEYIID from the company id (GVKEY) and the stock issue (IID).

If your data originally came from WRDS it was originally sorted by GVKEY/DATADATE (for company data) or by GVKEY/IID/DATADATE (for stock issue data).

Of course, you may have re-ordered it in your intermediate work. But I wouldn't be surprised if your data currently is sorted by GVKEY, if not already by GVKEY/IID/DATADATE (or maybe GVKEY/DATADATE/IID).

If so you can use space-saving and time-saving techniques to convert a dataset already sorted by group to a dataset sorted by detail within group.

In particular, if is is sorted by GVKEY, you don't need a proc sort. Instead you can (a) read in all details records for one GVKEY, (b) sort the detail records in memory by GVKEYIID/DATADATE and (c) output, (d) clear the in-memory data container. Then go to the next GVKEY and repeat.

This approach would take almost NO temporary disk space (and would be much faster). Here's how the code looks.

data want (drop=_:);
  set have;
  by gvkey;
  if _n_=1 then do;
    declare hash h (dataset:'have (obs=0)',ordered:'A',multidata:'Y');
     h.definekey('gvkeyiid','datadate');
     h.definedata(all:'Y');
   declare hiter hi ('h');
  end;
  h.add();
  if last.gvkey;
  do _rc=hi.first() by 0 until(_rc^=0);
    output;
    _rc=hi.next();
  end;
  _rc=h.clear();
run;

To check in advance whether the data are already ordered by GVKEY, just run:

data _null_;
  set have;
  by gvkey;
run;

If there are no error messages, then the order by gvkey is true. Obviously you could even do this by your specific variables to see if the desired final order already is in place.

Re: Problem with PROC SORT in a large dataset

Phil_NZ — Sun, 21 Mar 2021 19:51:33 GMT

Hi @Kurt_Bremser

Your suggestion confused me a little bit. So, TAGSORT is only good with the compressed datasets (by using options compress=yes). So, do you also mean the large dataset without being compressed by compress option will not work that well with TAGSORT?

Please let me know if I explain you wrongly.

Warmest regards.

Re: Problem with PROC SORT in a large dataset

SASKiwi — Sun, 21 Mar 2021 20:10:10 GMT

@Phil_NZ - From what I understand you have a remote SAS server available to you at your university, so why not switch to using it for dealing with large datasets rather than wasting a lot of time trying to getting them to fit on a PC?

Re: Problem with PROC SORT in a large dataset

Phil_NZ — Sun, 21 Mar 2021 20:10:20 GMT

Hi @mkeintz

Thank you for your specific suggestion. It seems that it is time for me to learn about the HASH objects because I also saw @Kurt_Bremser give me a lot of suggestions relating HASH OBJECT recently.

Warmest regards.

Phil

Re: Problem with PROC SORT in a large dataset

Phil_NZ — Sun, 21 Mar 2021 20:14:33 GMT

Hi @SASKiwi

Yes, from your explanation, I recognize that I am using the local machine to run a large dataset. I see it as a chance for me to learn something further about how to deal with a large dataset with limited memory. And now, the IT department in our school is dealing with people moving from COVID restriction level 3 to level 1, so I better learned by myself quite a bit and develop my knowledge as well.

Thank you for your dedicated and unconditional helping so far.

Warmest regards,

Phil

Re: Problem with PROC SORT in a large dataset

SASKiwi — Sun, 21 Mar 2021 20:37:16 GMT

@Phil_NZ - Understood. BTW you are studying at my old university 😀

Re: Problem with PROC SORT in a large dataset

Phil_NZ — Sun, 21 Mar 2021 21:46:08 GMT

Hi @SASKiwi

What a small world!! A greeting to Massey alumni! Thank you for your help and suggestion so far!

Warmest regards!

Phil.

Massey Business School

Re: Problem with PROC SORT in a large dataset

ChrisNZ — Mon, 22 Mar 2021 01:49:26 GMT

> the error message suggests that a utility file got too big.

Another option is to store the utility files in a compressed folder.