Run multiple queries concurrently in SAS

Question

I have 36 completely independent queries I need to run on a regular bases which would go much faster if they could run 3 at a time (the database cancels our queries if we try and do more than 3 at a time) instead of each one waiting for the previous to finish.

I would like to do something like this

/* Some prep code here*/

/* Launch batch 1 containing queries 1-12*/
/* Immediately launch batch 2 (13-24) without waiting for 1-12 to finish*/
/* Immediately launch batch 3 (25-36)*/

/* Wait until all 3 batches are done and run some conclusion code*/

Or, if possible, just give it the 36 queries all together and have it run multiple at a time making sure to not have more than 3 running at any given time and any time one finishes, just add the next one from the stack.

Is this possible to do using SAS?

Thanks

Answer 1

SAS Grid Computing? http://support.sas.com/rnd/scalability/grid/index.html

Answer 2

I'm assuming you have a SAS server and from your local machine you're launching the queries. (If you dont and work locally its not a problem you can do a rsubmit to a spawner you have on your local machine) Even with SAS/Base its possible to have 3 queries launch at the same time by having in a single code three connection. I'm assuming here you dont want to share work libraries and are completely independent queries

option autosignon=yes;
option sascmd="!sascmd";

* some random data;
data prova1;
do i=1 to 20000000;
    x=rand('UNIFORM');
    output;
end;
run;

data prova2;
do i=1 to 20000000;
    y=rand('UNIFORM');
    output;
end;
run;
*open connection to the server ;

options comamid=tcp;
filename rlink "D:\SAS\SASFoundation\9.2\connect\saslink\tcpwin.scr";
%LET host1=nbsimbol59;
%LET host2=nbsimbol59;

signon remote=host1 script=rlink;
signon remote=host2 script=rlink;

rsubmit process=host1 wait=no inheritlib=(work=cwork);; 

   proc sort data=cwork.prova1 out=cwork.r1;
     by x;
   run;

   proc sort data=cwork.r1 out=cwork.r1a;
     by i;
   run;


endrsubmit;


rsubmit process=host2 wait=no inheritlib=(work=cwork);; 

   proc sort data=cwork.prova2 out=cwork.r2;
     by y;
   run;

   proc sort data=cwork.r2 out=cwork.r2a;
     by i;
   run;

endrsubmit;

/* Wait for both tasks to complete. */
waitfor _ALL_ host1 host2;

data r9;
     merge r1a (in=a) r2a (in=b);
    by i;
    if a and b;
    run;


signoff host1;
signoff host2;

Only problem with this sample code is that it will wait both task to end and atm it doesnt come to mind a way to have it launch another query as soon as one ends but i believe it may be possible to have some way around it.

For now with this code you can easily launch 3 queries at a time, then when they end up 3 more and so on. For your other request i'll think about it :)

Answer 3

On some platforms (Windows and UNIX for sure), if the configuration allows your SAS session to interact with the OS, then the SYSTASK statement gives you the ability to executes, lists, or terminates asynchronous tasks. Combined with the WAITFOR statement, you can do something like this:

systask command "sas prog1.sas" taskname=sas1;
systask command "sas prog2.sas" taskname=sas2;
systask command "sas prog3.sas" taskname=sas3;
waitfor _all_ sas1 sas2 sas3; /* suspend current session until the three jobs are finished */

See documentation on SYSTASK and WAITFOR statements (for the Windows Platform).