topic Re: Way to resume PROC SORT from LCK file? in SAS Procedures

Way to resume PROC SORT from LCK file?

abeck19 — Mon, 05 May 2014 15:00:09 GMT

I was running a PROC SORT on a very large set of data (was running for about 2 weeks) when my SAS server rebooted and all jobs running were stopped. My sort was almost finished (of course!) and I don't have time to wait another 2 weeks for this job to re-run.

The LCK file from this sort is still in my work folder. Is there any way to pick up the job where it left off before the reboot using the LCK? Any suggestions/ideas will be very much appreciated!

Re: Way to resume PROC SORT from LCK file?

ballardw — Mon, 05 May 2014 15:55:35 GMT

It may be worth describing your data and the sort options used as that is a very long time for a sort to run.

I suspect you may not be able to do much with the dataset as long as that LCK file exists.

Re: Way to resume PROC SORT from LCK file?

jakarman — Mon, 05 May 2014 16:02:46 GMT

The .lck file is the temporary name that will be used to give the definitive name when ready. First deleting the original dataset when overwriting that.

As the sort process was in the merge stage there must be some #utl files in a saswork directory.

You cannot restart this process because there is no restart for the proc sort defined. As simple and possible frustrating start again at your last known correct state.

Possible you could use checkpoint restart in your code. SAS(R) 9.3 Language Reference: Concepts, Second Edition. The restart actions can be handed to operations guys.

Still it is not possible to restart somewhere in the middle when executing a proc,

Re: Way to resume PROC SORT from LCK file?

abeck19 — Mon, 05 May 2014 16:07:04 GMT

The data is information on impressions of some internet banner ads. Without going into too much detail, included in the data are the user's ID, ad ID, time, website, and some other identifiers. I am dealing with billions of impressions and thus billions of observations - hence the long sort time. I use the tagsort option and am sorting my data on user ID and time so that I can then "roll up" my data by user ID.

Having an admin delete the LCK and/or copying the dataset to a new name would not be an issue - I just don't really have the time to restart the entire sort process. Ideally there would be some way to resume the sort from where it left off using the LCK file but it seems that this is not possible.

Re: Way to resume PROC SORT from LCK file?

Peter_C — Mon, 05 May 2014 16:17:06 GMT

if you are doing any kind of roll-up as you handle your data, I would suggest:

having your internet-search-trawl create a view rather than data
roll-up that "view" with proc summary

then you only host summary data.

It is impractical to wait 2 weeks for a sort!

If my suggestion is not relevant because you only want to summarize the results of that internet search, try performing your routine in blocks of fifty million rows, interleave (set with a by statement) the results, then summarize that collection.

Depending on your data it might take only a day (or less)

good luck

Re: Way to resume PROC SORT from LCK file?

abeck19 — Mon, 05 May 2014 16:24:34 GMT

Performing the sort in blocks may work - even if it takes a day or two, that's better than another 2 weeks. Any chance you could provide an example/sample code? I would certainly call myself a SAS newbie and am not sure of how I would execute your suggestion.

Re: Way to resume PROC SORT from LCK file?

SASKiwi — Tue, 06 May 2014 00:49:18 GMT

Here is a simple example with two sort blocks:

proc sort data = data1;

by sort_vars;

run;

proc sort data = data2;

by sort_vars;

run;

data combined;

set data1 data2;

by sort_vars;

run;

The combined dataset maintains the sort order of the of the block1 and block2 datasets- this is interleaving as described by Peter C. Try it yourself on some test data.

Re: Way to resume PROC SORT from LCK file?

jakarman — Tue, 06 May 2014 06:30:50 GMT

Going further into details. Would avoid using Tagsort. SAS(R) 9.4 Companion for UNIX Environments, Third Edition ( SORT Procedure: UNIX) It is single threaded, normally the last rescue when you have disk-space problems. In a one run situation this will do, but when needing to run it more often there are smarter approaches.

Better tuning of your IO system (ask your admin). Splitting up a too big dataset in smaller portions and do the merge (as described by Peter C / SASkiwi ) will help.

Still having problems and there is also budget, than you could do some investigations on the mentioned host sort syncsort.

Re: Way to resume PROC SORT from LCK file?

Peter_C — Tue, 06 May 2014 06:52:41 GMT

Thank for domonstrating the interleave when I was off-line for so long....

I should add a little more:

Because the starting data set is so large, it would be great if it is already in a base SAS dataset. Then we can use dataset options to read only a part of the set

proc sort data=your.data( obs=20000000 firstobs=10000001 )

Out= set10e6 ;

by the by vars ;

Run;

That should sort the 10 million rows from row 10 million and 1 to row 20million

The macro language or even call execute in a datastep could automate this selection of blocks of data "by row number".

If you knew all the values of one of those sort keys, even better performance would be available.

PeterC

Re: Way to resume PROC SORT from LCK file?

Ksharp — Tue, 06 May 2014 11:26:03 GMT

Using the following code would be better for sorting a large table:

data F M;

set class;

if sex="F" then output F;

esle if sex="M" then output M;

run;

After that :

proc append ....

Xia Keshan

Re: Way to resume PROC SORT from LCK file?

jakarman — Tue, 06 May 2014 12:01:53 GMT

@Xia, nice approach it is making the data process easier. With some G-s (10**9) on input records and possible streamprocessing as the source are some internet-banners (click analysis) I would have expected you to go to using the hash-object approach. That can be an interesting way to solve some kind of problems. It think Abeck19 is not that far, we will lose him/

Re: Way to resume PROC SORT from LCK file?

Ksharp — Tue, 06 May 2014 12:21:44 GMT

Thanks. Actually I took this idea from someone( I don't remember ).

About Hash Table you said, it is easy, hash table has already built-in function to sort data, but That is not suitable for this situation( large table ), you know no one could have so huge memory .

data _null_;
if 0 then set sashelp.class;
 declare hash h(dataset:'sashelp.class',ordered:'a',multidata:'y');
  h.definekey('sex');
  h.definedata(all:'y');
  h.definedone();

  h.output(dataset:'want');
  stop;
  run;

Xia Keshan

Re: Way to resume PROC SORT from LCK file?

Peter_C — Tue, 06 May 2014 12:23:36 GMT

It is the roll-up of GBs of data that prompted a suggestion of proc summary operating on blocks or subsets. As proc summary is more memory-based and reduces i/o on output it provides a trade-off but demands memory so that is an issue to manage. Of course a hash approach would be top if the result set could sit in available memory.

To start discovery of potential for all these alternatives we would do a nLevels analysis of the sort keys or roll-up variables, with proc freq.

peter

Re: Way to resume PROC SORT from LCK file?

data_null__ — Tue, 06 May 2014 12:35:20 GMT

Maybe you saw this SAScomminity.org tip HASH sort (SAScommunity.org tip-of-the-day 08OCT2013)

I applied CALL VNEXT to generalize it somewhat.

Re: Way to resume PROC SORT from LCK file?

Peter_C — Sat, 10 May 2014 16:26:13 GMT

with a test run to create a 6GB sas dataset, then first sort it in blocks then do a rollup (proc summary) in blocks of increasing size, here is some code to demonstrate the blocks suggestion I was making

data tlarge ;

set sampsio.empinfo; * should be available, just try it;

do _n_=1 to 1e5;

output;

end;

run;

%let byv = divcode division ;

libname user ( './' work ) ;

data _null_;

retain start 1 ;

do exp=4 to 8 ;

block=10 ** exp ;

call execute( 'proc sort data= tlarge( firstobs=' !! put( start, 9.-L ) ) ;

call execute( ' obs = ' !! put( block, 9.-L ) ) ;

call execute( ') out=tlarg_' !! put( start, 9.-L ) ) ;

call execute( "; by &byv ; run ;" );

start = block+1 ;

end ;

run ;

data _null_;

retain start 1 ;

do exp=4 to 8 ;

block=10 ** exp ;

call execute( 'proc summary data= tlarge( firstobs=' !! put( start, 9.-L ) ) ;

call execute( ' obs = ' !! put( block, 9.-L ) ) ;

call execute( ') missing noprint nway ; ' );

call execute( "class &byv ; var _numeric_ ;" );

call execute( 'output sum= out= tlasum' !! put( start, 9.-L ) ) ;

call execute( "; run ;" );

start = block+1 ;

end ;

run ;

Re: Way to resume PROC SORT from LCK file?

gergely_batho — Mon, 12 May 2014 16:07:24 GMT

Hi abeck19,

My solution is similar then the previous ones, but is applicable only if you are lucky enough, that your dataset is sorted by date/time. (Not by id and then by time as you require) Since you have web log data it is quite likely... In the following example it is enough if it is groupped by day.

And I also assume, you don't need a raw sort, but some kind of aggregation.

proc summary data=in nway;

var amount;

class id;

by datetime;

format datetime datetime7.;

output out=out sum=;

run;

Now you have data grouped by day and id. Hopefully it is smaller, and you can process it. You can control the aggregation level with the format statement.

Everyone: Is this really faster: Dividing a dataset, sorting separately, then interleaving? This is what almost all sort algorithms are supposed to do internally (called "merge sort").

Re: Way to resume PROC SORT from LCK file?

Peter_C — Mon, 12 May 2014 17:16:00 GMT

The big problem in SAS sorting is the sortwork areas. These demand at least twice the original filesize and perhaps 3 or 4 times. And that's before it completes in a reasonable time.

proc summary uses memory arrays to collect its stats - providing a different constraint. Despite that it privides what I think is the simplest approach.

As I (weakly) demonstrated earlier, proc sumnary can be executed in hadoop-style blocks. (I should have extended the demo to show consolidation /summarization / rollup of the blocks)

Re: Way to resume PROC SORT from LCK file?

jakarman — Mon, 12 May 2014 17:26:26 GMT

It has always been the best approach to make your data smaller while not losing information. It will always be so when you are going beyond the comfort zone of your machine.

It will never have unlimited speed and unlimited storage resources. As at the moment you are hitting that you need to think harder. More clever approach to the data or you analyses.

@Gergely, your proposal is going back to that question. When the assumptions are not met still an issue. It also depend how often it should be executed.

A Hadoop style is asking a lot of storage, even more as just sorting. It is designed to be most read-only access while have a lot of spread data duplications.

Re: Way to resume PROC SORT from LCK file?

gergely_batho — Tue, 13 May 2014 09:51:06 GMT

Hi All,

I know you know it, but still want to clarify: The example above also executes in blocks. Because of the by statement.

If daily data does not fit into memory, just change the format. Flexible.