topic Re: Can we insert observations from multiple sources into a sas dataset same time in SAS Programming

Can we insert observations from multiple sources into a sas dataset same time

nbonda — Wed, 01 Jul 2015 13:01:50 GMT

I have a multiple macros those select record conditionally from database tables and insert output records into a sas datasets.

some times these macros run parallel and insert resulting records into a same dataset.

I am using proc sql to insert records, but I am losing some of the records.

is there other way we can insert processed records into a dataset.

Re: Can we insert observations from multiple sources into a sas dataset same time

Peter_C — Wed, 01 Jul 2015 22:05:11 GMT

Is SAS/SHARE available

it was designed to support such concurrent update

Re: Can we insert observations from multiple sources into a sas dataset same time

SASKiwi — Wed, 01 Jul 2015 22:14:42 GMT

How long do your inserts take? There is an option FILELOCKWAIT you can use to get SAS to wait for a locked SAS dataset to free up so in effect the inserts happen sequentially.

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

This option can be applied on a LIBNAME statement which you would need to use just prior to the SQL insert, then clear the LIBNAME immediately afterwards just to be safe.

Re: Can we insert observations from multiple sources into a sas dataset same time

Patrick — Wed, 01 Jul 2015 22:34:49 GMT

The ways I know of how to achieve or simulate concurrent write to SAS tables:

1. SAS/Share

2. Filelockwait

3. Each process writes to its own table but you have a view over these tables. You then can use the view for downstream processing.

....ideally: You use a database table instead which allows concurrent write (eg. a table in Oracle).

Re: Can we insert observations from multiple sources into a sas dataset same time

Peter_C — Wed, 01 Jul 2015 23:07:44 GMT

I think a VIEW is the way to go.

On great feature it provides is the USING clause to define a libref / connection with options like FILELOCKWAIT