topic Re: SAS stored macro - VBA - "lock is not available" error in Microsoft Integration with SAS

SAS stored macro - VBA - "lock is not available" error

gabriel_k — Mon, 27 Jun 2016 08:29:29 GMT

Hello,

working on an application in Excel that generates charts and tables from the tables previously analysed by SAS. I use VBA (Windows client and IOM Bridge connection) to run SAS stored macro and then getting results with ADODB. Once running the code SAS tables are locked even if the Workspace was closed and i'm getting "lock is not available" error. How can i solve this problem ? (basicly i need to unlock the tables just to delete them).

Re: SAS stored macro - VBA - "lock is not available" error

Kurt_Bremser — Mon, 27 Jun 2016 08:49:56 GMT

I guess you meant "stored process".

Do you really need to update persistent files from the stored process?

Or do you simply create tables for transfer to Excel in a location other than WORK? In which case, I'd move that to WORK to avoid unnecessary collisions.

Re: SAS stored macro - VBA - "lock is not available" error

gabriel_k — Mon, 27 Jun 2016 09:04:07 GMT

I use stored compiled macros:

http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/viewer.htm#a001328775.htm

But You have a reason, i'll store my tables in Work library, i just wanted to have a possibility to look on tables thrue the SAS Guide.

There are some other tables i use to retrieve information from different libraries and change rows if needed, so i need to drop the lock anyway.

Re: SAS stored macro - VBA - "lock is not available" error

Kurt_Bremser — Mon, 27 Jun 2016 09:22:53 GMT

@gabriel_k wrote:
..... i just wanted to have a possibility to look on tables thrue the SAS Guide.

This is probably the source of your problems. Viewing a dataset through EG will always prevent the lock that is needed for update.

If you just need to update specific parts of datasets, SAS/SHARE might help (if you have it licensed and set up).

My method to prevent ERRORs caused by open datasets is specific to UNIX. To update a dataset, I do the update in WORK, then I use the operating system to remove the link (directory entry) to the file (UNIX rm command), and then recreate the dataset from the table in WORK.

This is possible because the rm removes the reference, but not the file. Only when a file is found to have no further reference defined, the physical data is removed once the last handle closes.