topic Re: SORT 70G of data in SAS Programming

SORT 70G of data

pinkY2229 — Fri, 14 Oct 2016 09:46:45 GMT

Hello,

When I'm trying to sort a table with 70Go I've the "DISK full" message...

But I've a WORK with 903Go 😞

Usually i'm expecting that a SORT will not use above 70Go * 3 of space

Do you have an idea why i'm facing this kind of pb?

The table is like this :

100 561 233 rows
270 columns
Observation Length : 14 284
Table is compressed (CHAR)

To circumvent the pb, i'm currently trying TAGSORT, I'will also trying to split the data in 10 sub-tables.

Re: SORT 70G of data

Shmuel — Fri, 14 Oct 2016 10:15:10 GMT

There are probably other datasets in WORK from previous steps and/or from parallel sessions running at same time.

One of the solutions (maybe not the best) is to split the 70GB dataset into several smaller ones,

sort eache saparetly and finally merge them back.

Re: SORT 70G of data

RW9 — Fri, 14 Oct 2016 10:21:52 GMT

What is 70Go?

Some things to check, do you have anything else in Work?

How are you sorting the data?

Where is the data residing, is it on a network or locally?

Re: SORT 70G of data

pinkY2229 — Fri, 14 Oct 2016 10:35:03 GMT

thank u for your message.

size is 70Gb (gigas)

I'v nothing else in my work : 'im monitoring the work every minute with df ('im under AIX) and I can see only my WORK Folder growing, growing...

The table is already in the WORK and I want to sort it .

Re: SORT 70G of data

Reeza — Fri, 14 Oct 2016 10:45:24 GMT

Are you sure you have access to full size? IT can often limit work space.

Re: SORT 70G of data

RW9 — Fri, 14 Oct 2016 10:45:39 GMT

Perhaps this article will help - particularly sortsize:

http://support.sas.com/documentation/cdl/en/hostunx/67929/HTML/default/viewer.htm#n1svfqjvm6a2sfn1abq0g48bhymm.htm

http://support.sas.com/documentation/cdl/en/hostwin/67962/HTML/default/viewer.htm#n0ea63jfjic0vpn15dbdh2ee0xbb.htm

Not sure how you plan on working with that data, its a big chunk anyways you look at it (i wouldn't personally be happy with 270 columns - anymore than 20 or so is difficult programming wise).

Re: SORT 70G of data

Reeza — Fri, 14 Oct 2016 10:46:05 GMT

Are you sure you have access to full size? IT can often limit work space.

Also, what's the exact error you receive?

Re: SORT 70G of data

Kurt_Bremser — Fri, 14 Oct 2016 10:46:43 GMT

The utility file of a proc sort is not compressed, so you can deduct its size from the size of the file and the compression factor shown in the log when you create it.

Depending on the contents, your compression ratio could well be above 90%, and then it is no surprise you run out of space.

Using tagsort is the right remedy.

I'd also consider adding additional disk space that is physically separate from your WORK, and set UTILLOC to it. That prevents concurrent read and write on the WORK disks.

Re: SORT 70G of data

pinkY2229 — Fri, 14 Oct 2016 10:55:28 GMT

Indeed,

I have a ratio of 95.43 percent (!?)

70Gb * 95.43 percent.= something below 700, but 700 into * 3 > 900Gb WORK folder.

tagsort did the job :

NOTE: PROCEDURE SORT used (Total process time):
      real time           1:41:46.94
      user cpu time       27:57.53
      system cpu time     4:36.74
      memory              266864.43k
      OS Memory           272352.00k

Re: SORT 70G of data

Kurt_Bremser — Fri, 14 Oct 2016 11:02:56 GMT

Actually, your compressed file has less than 5% of its uncompressed size. So the uncompressed file would be roughly 20*70 GB in size, and that amounts to 1.4 TB(!).

When sorting large compressed datasets with a considerable compression rate (>80%), I always use TAGSORT, just to prevent a disk full condition in my UTILLOC.

Re: SORT 70G of data

Ksharp — Sat, 15 Oct 2016 06:53:24 GMT

For big table, I would like to split it into small tables and combine them together later. Like:

data F M;
 set sashelp.class;
 if sex='F' then output F;
 else if sex='M' then output M;
run;

data want;
 set F M;
run;