Learning SAS? Welcome to the exclusive online community for all SAS learners.

Data too large? 7.5GB SAS file - cannot sort.

Reply
New Contributor
Posts: 2

Data too large? 7.5GB SAS file - cannot sort.

Hello,

I am working with a 7.5GB SAS file with over 43 million observations on 15 columns.  I am unable to sort or perform any other kind of simple operation.  I looked at but it seems the fix of adding the options user command didn't help.  I'd really appreciate any help!

The code is as follows:

OPTIONS USER='/folders/myfolders/';

%web_drop_table (WORK.Sort);

title;

footnote;

proc sort data=_TEMP0.QUARTERLY_INSTITUTIONAL_FILINGS out=WORK.Sort;

    by CUSIP MGRNAME;

run;

quit;

title;

footnote;

%web_open_table(WORK.Sort);

The following is the log result:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

57 

58 /*

59 *

60 * Task code generated by SAS Studio 3.3

61 *

62 * Generated on '6/11/15, 8:58 PM'

63 * Generated by 'sasdemo'

64 * Generated on server 'LOCALHOST'

65 * Generated on SAS platform 'Linux LIN X64 2.6.32-504.12.2.el6.x86_64'

66 * Generated on SAS version '9.04.01M2P07232014'

67 * Generated on browser 'Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/43.0.2357.124

67 ! Safari/537.36'

68 * Generated on web client

68 ! 'http://192.168.32.128/SASStudio/33/main?locale=en_US&zone=GMT-04%253A00&preventCache=1434070041110'

69 *

70 */

71 OPTIONS USER='/folders/myfolders/';

72 

73 %web_drop_table (WORK.Sort);

74 title;

75 footnote;

76 

77 proc sort data=_TEMP0.QUARTERLY_INSTITUTIONAL_FILINGS out=WORK.Sort;

NOTE: Data file _TEMP0.QUARTERLY_INSTITUTIONAL_FILINGS.DATA is in a format that is native to another host, or the file encoding

  does not match the session encoding. Cross Environment Data Access will be used, which might require additional CPU resources

  and might reduce performance.

78 by CUSIP MGRNAME;

79 run;

ERROR: No disk space is available for the write operation. Filename =

  /tmp/SAS_util0001000006DC_localhost.localdomain/ut06DC000002.utl.

NOTE: The SAS System stopped processing this step because of errors.

ERROR: Failure while attempting to write page 2166 of sorted run 8.

NOTE: There were 37023745 observations read from the data set _TEMP0.QUARTERLY_INSTITUTIONAL_FILINGS.

ERROR: Failure while attempting to write page 43704 to utility file 1.

WARNING: The data set WORK.SORT may be incomplete. When this step was stopped there were 0 observations and 15 variables.

ERROR: Failure encountered while creating initial set of sorted runs.

ERROR: Failure encountered during external sort.

NOTE: PROCEDURE SORT used (Total process time):

  real time 2:28.64

  cpu time 2:39.23

  

80 

81 quit;

82 title;

83 footnote;

84 %web_open_table(WORK.Sort);

85 

86 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

98 

Valued Guide
Posts: 3,208

Re: Data too large? 7.5GB SAS file - cannot sort.

Sort is only limited by the physical resources versus time duration.

The message you are into is the one of the Sas work. That one could be increased to much higher values in local installations. As  you are in UE version SAS has decided to have it low as it is for educational not production environments

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

Re: Data too large? 7.5GB SAS file - cannot sort.

So you're saying that it's not possible to sort a file that large in UE?

Super User
Posts: 6,964

Re: Data too large? 7.5GB SAS file - cannot sort.

SAS UE is a learning tool, created to handle practice data. What you are trying to do clearly falls into production, not learning or practice.

Look at how much disk space your VM occupies, then think that a sort needs 3 times the disk space of the original file during processing: original file, utility file, new file.

If your date file is stored with the compress=yes option, the utility file will even be larger, as it is not compressed, but holds all the data.

You could try the tagsort option, but for data this large you will probably need a real SAS installation.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 10,538

Re: Data too large? 7.5GB SAS file - cannot sort.

You can try the TAGSORT option on the Proc Sort statement to reduce memory usage. But the comments about production versus learning may still apply.

Contributor
Posts: 52

Re: Data too large? 7.5GB SAS file - cannot sort.

you can try breaking the original file into, say, 50 bins, the bins are ordered. Bin1 can cover aa* to aq*, Bin2 can cover ar* to az*. Etc.

Proc sort each bin.

Then join the bins together.

I believe there is at least 1 SUG paper on this method.

Ask a Question
Discussion stats
  • 5 replies
  • 769 views
  • 0 likes
  • 5 in conversation