topic Re: Automate SAS code to wait for sorted datasets in SAS Programming

Automate SAS code to wait for sorted datasets

JJP1 — Fri, 17 May 2019 03:56:12 GMT

Hi ,

Would you please help on how can we automate code for below steps please

1. get number of records from SAS dataset

2.split the dataset into 8 jobs based on the number of records(approximately 1 million) and sort the splitted datasets based on the key and run these 8 jobs in parallel

3.also i need to create step where it should wait and check whether these splited datasets are sorted successfully,so that we can proceed with next step

Re: Automate SAS code to wait for sorted datasets

ChrisNZ — Fri, 17 May 2019 04:35:24 GMT

Unless the 8 jobs can do their sort in memory or use their own disks, this could be be much slower than a single sort (or even successive sorts) as the jobs will be competing for the same disk resources.

Re: Automate SAS code to wait for sorted datasets

JJP1 — Fri, 17 May 2019 05:43:39 GMT

Thanks.Would you please help on coding part where we can split the dataset(that is having 800 millions +records)

into 1 million records of data set(8 subsets of datasets) and sort simultaneously so that we can reduced the time taking.

please how can we approach this please.actually i was using split 1 macro which i found in SAS papers and splitting the datasets into number of subsets.

but we should sort simultaneously as and when the splitted data set gets created. this part i stuck please help. also i need to create wait step for these to wait till these data sets completed please

Re: Automate SAS code to wait for sorted datasets

Patrick — Fri, 17 May 2019 06:11:27 GMT

@JJP1

Why don't you just go for a multi threaded sort. That will likely perform better than anything you can manually code for.

https://go.documentation.sas.com/?docsetId=proc&docsetTarget=p0guut2xk8yz2yn17ibn9nwcyx8v.htm&docsetVersion=9.4&locale=en#p0lxobanuchmjin1daufy56f496w

If that's still not good enough then investigate how UTILLOC is assigned and eventually try to spread this out over multiple disks (for better disk I/O).

And last but not least: Consider creating the source and sorted target table using the SPDE engine and also make sure that the data chunks and SPDEUTILLOC gets spread out over multiple disks.
https://support.sas.com/documentation/cdl/en/engspde/69752/HTML/default/viewer.htm#p14iytucvmskdtn1qeye9zkpp0ss.htm

Re: Automate SAS code to wait for sorted datasets

Kurt_Bremser — Fri, 17 May 2019 08:27:57 GMT

You need to fit your computing and storage power to the size of your data. SAS sorting tools (proc sort, sorting in SQL, implicit sorting in SPDE) are already multi-threaded, and can use proper infrastructure if it's there. If the infrastructure is not there, you can't magically create better performance. Your split/sort/merge will most probably be worse than what you have now.

Separate WORK and UTILLOC. Use striped, high-quality SSD's for these volumes. Have enough cores.

If you want to split, do not run the sorts in parallel, run them one after another, to avoid contention on the disks (especially if you still use spinning metal). Write that first without macro code, see if it helps at all, and once that is confirmed, make it dynamic.

Re: Automate SAS code to wait for sorted datasets

JJP1 — Fri, 17 May 2019 09:37:21 GMT

ok. thank you.would you please help on i need to create a wait step to wait till sort operation completes

would you please help on this

Re: Automate SAS code to wait for sorted datasets

Kurt_Bremser — Fri, 17 May 2019 09:50:56 GMT

Read the last paragraph of my post again.

Re: Automate SAS code to wait for sorted datasets

JJP1 — Fri, 17 May 2019 10:11:17 GMT

yes,i have decided to run manually with out any macros',so now i can not run currently as we are facing with some slowness issue in our SAS server

so i am just preparing to do this whenever environment is back and stable

so i am aware of splitted and sorting process (i mean writing the code).

bit i don't know how to write code to wait till sorted datasets gets completed please.

Also sorry to ask again,i am not clear on below statement ,please help

"Separate WORK and UTILLOC. Use striped, high-quality SSD's for these volumes. Have enough cores."

how can we follow above approach please.

1.kindly confirm is this the above approach you are suggesting will be better than whatever i posted in this thread(split ,sort and merge parallely) please.

Re: Automate SAS code to wait for sorted datasets

Kurt_Bremser — Fri, 17 May 2019 10:44:45 GMT

When you run the sorts in succession (eg in a macro %do loop), you can interleave/concatenate the resulting datasets as soon as the loop is finished, no need for a special wait.

Having infrastructure that lets you use simple code without any artificial optimizations is always to be preferred. Especially if the problem occurs repeatedly.

In my ~1000 SAS batch jobs, I have exactly one where I split a large dataset into subsets, and that was not because of performance, but because the required sort would use so much of UTILLOC that other, concurrent jobs would not have enough left. Mind that the overall performance is considerably worse than if I ran it the usual way, but then I would only be able to test it through the main batch job production user which has no quotas defined. Something you do not want, period.

So I do this

dynamically split along the contents of the first original "by" variable
sort individually, and run proc means against each subset, using only the second and following "by"variables
stack the result subsets in sorted order, thereby emulating a sort of the top level "by" variable

IIRC, I do not even use macro code. I create a control dataset with the distinct values of the top-level by variable, and use call execute repeatedly to work through the data one value at a time.

Re: Automate SAS code to wait for sorted datasets

ChrisNZ — Fri, 17 May 2019 10:50:45 GMT

Kurt, For that massive sort, have you tried option tagset instead? Not saying it will be better, wondering rather.

Re: Automate SAS code to wait for sorted datasets

Kurt_Bremser — Fri, 17 May 2019 11:12:11 GMT

@ChrisNZ wrote:
Kurt, For that massive sort, have you tried option tagset instead? Not saying it will be better, wondering rather.

I often use tagset for datasets that produce over-large utility files (because of the compress option), but in this case, the "big chunk" was so large that I already had to "dance" across multiple workspaces to even create it. And when a new hierarchy level with considerable cardinality was added that prevented me from using class in proc means, the sort would either be too resource- (usual sort) or time- (tagset) consuming, so I opted for the split, where I could delete the master dataset after splitting, and the individual steps ran in a decent timeframe. Mind that I don't create the big dataset any longer, I already create splitted datasets in the first place now.

If this happened to me more than once, I would surely have decided to increase my UTILLOC and workspaces.

As the resulting code is far from "elegant" and therefore violates several of my Maxims, it is a clear exception to the rules. And it was one of the things I went through in detail when I started introducing my successor on the job, so he won't have a bad surprise the first time he has to do maintenance on it.

Re: Automate SAS code to wait for sorted datasets

ChrisNZ — Fri, 17 May 2019 11:19:45 GMT

Thanks for the insight. And I presume you split with a where clause on the sort key rather than by obs, so the sorted data sets are ready to use?

Re: Automate SAS code to wait for sorted datasets

Kurt_Bremser — Fri, 17 May 2019 15:18:23 GMT

@ChrisNZ wrote:
Thanks for the insight. And I presume you split with a where clause on the sort key rather than by obs, so the sorted data sets are ready to use?

Yes. That way the final step after the summaries is a very simple data step that concatenates everything, without even a by for interleaving needed.

Re: Automate SAS code to wait for sorted datasets

ChrisNZ — Fri, 17 May 2019 23:20:45 GMT

Yes. SET BY really kills speed compared to SET. Proc append rather than a data step might cut a bit of time too. Thanks again for sharing.

Re: Automate SAS code to wait for sorted datasets

Patrick — Sat, 18 May 2019 01:26:04 GMT

@JJP1

If you read what @Kurt_Bremser writes in this track then you'll understand that the manual approach you want to take is only suitable for rare exceptional cases. What's your reason for splitting up the source table?

If space restrictions in work/utilloc are the issue and the sort keys fit into memory then one coding alternative is the use of a hash table.

I get in my environment comparable run times with below sample data for all 3 sort steps.

options fullstimer;
data have(compress=yes);
  array bigvars{30} $100. (30*'A bbb cccc');
  do key1=8000 to 1 by -1;
    key2=rand('integer', 100);
    do key3=1000 to 1 by -1;
      output;
    end;
  end;
  stop;
run;

data want_hashsort(sortedby=key1 key2 key3 compress=yes);
  if _n_=1 then
    do;
      length _rownum 8;
      dcl hash h1(multidata:'y', ordered:'y');
      dcl hiter hh1('h1');
      h1.defineKey('key1','key2','key3');
      h1.defineData('_rownum');
      h1.defineDone();

      do while(not _last);
        set have(keep=key1 key2 key3) end=_last;
        _rownum+1;
        _rc =h1.add();
      end;
    end;

  _rc = hh1.first();
  do while (_rc = 0);
    set have point=_rownum;
    output;
    _rc = hh1.next();
  end;
  stop;
run;

proc datasets lib=work nolist nowarn;
  delete want_:;
  run;
quit;

proc sort data=have out=want_procsort1(compress=yes);
  by key1 key2 key3;
run;

proc datasets lib=work nolist nowarn;
  delete want_:;
  run;
quit;

proc sort data=have out=want_procsort2(compress=yes) tagsort;
  by key1 key2 key3;
run;

proc datasets lib=work nolist nowarn;
  delete have want_:;
  run;
quit;

Real Times

Hash: 1:04

Sort1: 1:16

Sort2: 0:53

From what I understand TAGSORT does actually something pretty similar to what above HASH approach uses - so eventually TAGSORT is the way to go if work space is the issue even though the sort is no more threaded.

Re: Automate SAS code to wait for sorted datasets

JJP1 — Mon, 20 May 2019 06:44:08 GMT

Hi @Patrick ,

The reason for splitting up for source tables is

1.the source table has 800+ million of records and this table has to join with some other tables and finally create target dataset.

2.initially we tried like doing joining with proc sql and getting the final output.but this takes a long time and it will not be completely processing this ,i will get errors like "insuffcient space".so we decided to split the tables and join or merge in order to complete this whole process with very less time please.

3.main aim to split is to do this whole process with very less time and avoid to get space issues.

Iam really trying to understand what @KurtBremser mentioned in this thread.as i am unable to grasp fully please and i am not sure how can i proceed on this please.sorry to trouble even after so much explanation i am asking like this please.

Would it be possible to help how hash code mentioned below works please and whether it will split please

Re: Automate SAS code to wait for sorted datasets

Patrick — Mon, 20 May 2019 08:03:58 GMT

@JJP1

Thank you for explaining the reason for splitting. That helps. And no reason for apologies. I'd say you get that much "resistance" from "us" because we don't want you to go down the wrong rabbit hole.

Nothing will be "very less time" with that big tables but there are potentially ways to combine data from different sources without the need to sort everything physically on disk. What's possible and "best" depends on what you have and what you need.

I believe it would be worth to look into the bigger problem to not solve something that eventually doesn't require solving (like coding for a "split-sort"). For us to give some guidance for the bigger challenge you would need to provide more info like:

1. Name of source tables (assumed all stored as SAS tables, else please specify if any database involved)

2. Number of rows per source table and eventually also the size per table

3. The code of the actual join which you've got working already for lower volumes (so we can understand the join logic)

4. The length of the variables used for joining (so we can estimate memory consumption for any in-memory approach)

5. Is there any need for the resulting target table to be sorted in a specific way for further downstream processing.

Re: Automate SAS code to wait for sorted datasets

Kurt_Bremser — Mon, 20 May 2019 06:59:49 GMT

Whenever you run into "insufficient disk space" while doing a join in SQL (or experience unexpectedly bad performance), you positively need to test the basic SAS method of sorting and doing a data step merge before you hare off into really complicated coding, provided you do not need the SQL capability of creating a cartesian product. If a simple proc sort of a dataset is not possible (even when using the tagset option), then you need to either work on your infrastructure or go into complex coding.

But whatever you do, sorting in sequence is to be preferred over sorting in parallel.

Re: Automate SAS code to wait for sorted datasets

JJP1 — Mon, 20 May 2019 08:07:36 GMT

Thanks @Kurt_Bremser for kind reponse .

Hi @Patrick ,

please find the details below

1. Name of source tables (assumed all stored as SAS tables, else please specify if any database involved)

All stored as SAS tables

2. Number of rows per source table and eventually also the size per table

Size
Library of Size of
Name Member Name File File
-------------------------------------------------------------

XXX AAA 2GB 1931520K
XXX BBB 102GB 1.0673E8K
XXX CCC 1GB 1445376K
XXX DDD 3GB 2657024K
XXX EEE 1GB 1227008K
XXX FFF 2GB 2466048K

Table_Name Record count
AAA - 22758772
BBB - 8.1021E8
CCC - 28754734
DDD - 27547919
EEE - 24549490
FFF - 24499890

3. The code of the actual join which you've got working already for lower volumes (so we can understand the join logic)

tried joining small tables and placed in one permanent dataset and sorted large dataset and placed in one permanent dataset. and tried merging.but it takes more time and insufficient space issue

code for actual join is

PROC SQL;
   CREATE TABLE joined1 AS 
   SELECT t1.AAAAAAAAAAA, 
          t1.PPPPPPPPPPPP, 
          t1.CCCCCCCCCCCC, 
          t1.DDDDDD, 
          t1.TTTTTTTTTTT, 
          t1.SSSSSSSSS, 
          t1.GGGGGGGGGGGGG, 
          t2.KKKKKKKKKKKK, 
          t2.FFFFFFFFFFFF, 
          t2.YYYYYYYY, 
          t2.RRRRRRR, 
          t2.EEEEEEEEEEEE, 
          t2.UUUUUUUUUUUUU, 
          t2.ZZZZZZZZZZZZZZZZ, 
          t2.LLLLLLLLLLLL, 
          t3.JJJJJJJJJ, 
          t3.NNNNNNNNNNNNNN, 
          t4.OOOOOOOOOOOO, 
          t4.VVVVVVVVVVVV, 
        
      FROM AAA t1
           left JOIN FFF t2 ON (t1.AAAAAAAAAAA = t2.AAAAAAAAAAA)
           left JOIN EEE t3 ON (t2.KKKKKKKKKKKK = t3.KKKKKKKKKKKK)
           left JOIN CCC t4 ON (t3.JJJJJJJJJ = 
          t4.JJJJJJJJJ);
QUIT;

PROC SQL;
   CREATE TABLE joined2 AS 
   SELECT t1.*, 
          t2.YYYYYYYYYYYYYYY, 
          t2.SAMPLE, 
          t2.SAMPLE1, 
          t2.SAMPLE2, 
          t2.SAMPLE3, 
          t2.SAMPLE4, 
          t2.SAMPLE5, 
          t2.SAMPLE6, 
          t2.SAMPLE7, 
          t2.SAMPLE8, 
          t2.SAMPLE9, 
          t2.SAMPLE10
      FROM joined1 t1
           left JOIN DDD t2 ON (t1.OOOOOOOOOOOO = t2.OOOOOOOOOOOO);
QUIT;


proc sort data=xx.BBB out=BBB;(large dataset)
  by YYYYYYYYYYYYYYY QQQQQQQQQQ;
run;
proc sort data=joined2;
  by YYYYYYYYYYYYYYY;
run;
data joined3;
  merge joined2(in=ina) BBB(in=inb);
  by YYYYYYYYYYYYYYY;
  if ina;
run;

4. The length of the variables used for joining (so we can estimate memory consumption for any in-memory approach)

the length of the columns i m using to join the columns are : 16,23,32,28,23

please excuse me for mentioning in code as table names and columns names as sudo values please

5. Is there any need for the resulting target table to be sorted in a specific way for further downstream processing

Yes The resultant target table further needs to be subset-ted to 3 different dataset based on different conditions please

Re: Automate SAS code to wait for sorted datasets

Kurt_Bremser — Mon, 20 May 2019 08:18:41 GMT

@JJP1 wrote:

PROC SQL;
   CREATE TABLE joined1 AS 
   SELECT t1.AAAAAAAAAAA, 
          t1.PPPPPPPPPPPP, 
          t1.CCCCCCCCCCCC, 
          t1.DDDDDD, 
          t1.TTTTTTTTTTT, 
          t1.SSSSSSSSS, 
          t1.GGGGGGGGGGGGG, 
          t2.KKKKKKKKKKKK, 
          t2.FFFFFFFFFFFF, 
          t2.YYYYYYYY, 
          t2.RRRRRRR, 
          t2.EEEEEEEEEEEE, 
          t2.UUUUUUUUUUUUU, 
          t2.ZZZZZZZZZZZZZZZZ, 
          t2.LLLLLLLLLLLL, 
          t3.JJJJJJJJJ, 
          t3.NNNNNNNNNNNNNN, 
          t4.OOOOOOOOOOOO, 
          t4.VVVVVVVVVVVV, 
        
      FROM AAA t1
           left JOIN FFF t2 ON (t1.AAAAAAAAAAA = t2.AAAAAAAAAAA)
           left JOIN EEE t3 ON (t2.KKKKKKKKKKKK = t3.KKKKKKKKKKKK)
           left JOIN CCC t4 ON (t3.JJJJJJJJJ = 
          t4.JJJJJJJJJ);
QUIT;

PROC SQL;
   CREATE TABLE joined2 AS 
   SELECT t1.*, 
          t2.YYYYYYYYYYYYYYY, 
          t2.SAMPLE, 
          t2.SAMPLE1, 
          t2.SAMPLE2, 
          t2.SAMPLE3, 
          t2.SAMPLE4, 
          t2.SAMPLE5, 
          t2.SAMPLE6, 
          t2.SAMPLE7, 
          t2.SAMPLE8, 
          t2.SAMPLE9, 
          t2.SAMPLE10
      FROM joined1 t1
           left JOIN DDD t2 ON (t1.OOOOOOOOOOOO = t2.OOOOOOOOOOOO);
QUIT;


proc sort data=xx.BBB out=BBB;(large dataset)
  by YYYYYYYYYYYYYYY QQQQQQQQQQ;
run;
proc sort data=joined2;
  by YYYYYYYYYYYYYYY;
run;
data joined3;
  merge joined2(in=ina) BBB(in=inb);
  by YYYYYYYYYYYYYYY;
  if ina;
run;

And where in this code does the "insufficient disk space" happen? While sorting the large table, or during the merge?

Please post the whole log of that failing step.