running Proc Sql jobs in parallel and in sequence

[pemfir]

I have a rather straight forward questions.

lets assume i am using proc sql to create tables/data sets and save them into a machine.

suppose i have created the following files to collect my data : sqlquery1.sas , sqlquery2.sas, sqlquery3.sas, sqlquery4.sas

sqlquery1.sas  and sqlquery2.sas can be run in parallel

sqlquery3.sas  needs data from sqlquery1.sas  and sqlquery2.sas and should be run after they are finished

sqlquery4.sas  needs data from sqlquery3.sas and should start after sqlquery3.sas is finished.

I know if i create a sas file with the following commands

%include "~/sqlquery1.sas";

%include "~/sqlquery2.sas";

%include "~/sqlquery3.sas";

%include "~/sqlquery4.sas";

the files are going to be run in sequence. Is there a way to specify certain jobs to run in parallel while others to wait ?

Thank you,

[ChrisBrooks]

Probably the easiest way to do this is if you have SAS/CONNECT - you can run sqlquery1 and sqlquery2 on the same machine using MP CONNECT in parallel and by using the WAITFOR statement hold sqlquery3 until those jobs have finished.

You'll find an example here -> SAS/CONNECT(R) 9.2 User's Guide

[LinusH]

And if you don't have SAS/CONNECT, you need to look for building/maintain this logic outside SAS, since a single AS session can't execute different program steps in parallel.

The options could building a DOS/UNIX shell scripts, to call SAS with each .sas as a parameter.

If you license any part of Intelligence Platform, you could build flows of jobs in SAS Management Console.

[esjackso]

As you can see it will more than likely depend on your SAS setup at your site, so more information there might help refine the answers. I havent checked this out but I seem to remember doing something similar in Enterprise guide by creating multiple flows in a project that converge once the steps need to be sequential.

EJ

[pemfir]

Thank you guys a bit of clarification

I am not sure what you mean by my setup as i am new to SAS. I am not sure what SAS Connect is.

Let us assume all 4 queries sqlquery1.sas , sqlquery2.sas, sqlquery3.sas, sqlquery4.sas, uses local data stored in my local machine.

I am just running simple things such as

sqlquery1.sas  and sqlquery2.sas are something like this

proc sql,

create table A as

select * from (a local table)

run;

sqlquery3.sas then joins the results from both tables.

is there a simple way of doing this without SAS/Connect ?

[esjackso]

what version of sas are you using locally?

This will show at the top of the log window when you start SAS.

Eric

[pemfir]

version 9.3

[ChrisBrooks]

If Walt's suggestion doesn't help because you're running on Windows could you submit the following code

proc setinit;

run;

This will write a list of your licensed SAS products to the log; could you then copy and paste that list here so we can work out which solution might be best for you.

[WaltSmith]

I do this sort of thing frequently on Unix (Solaris actually). The following script shows the fundamentals:

#!/usr/bin/ksh

sas9 sqlquery1 &

sas9 sqlquery2 &

wait

sas9 sqlquery3

sas9 sqlquery4

the first line announces that it is a Korne shell script and that the OS should use the Korne shell interpreter (ksh) located at /usr/bin/ksh. The second line invokes the SAS executable (called sas9 on our machine but may be different on yours) and the ampersand following the sas9 invocation says to run in the background. Likewise for the next program sqlquery2. The wait command says to wait until background processes are finished then proceed. The following two invocations of sas are done sequentially since they are not followed by an ampersand.

If running under Windows then none of the above applies