How to import /read Excel file where Another person using the Same file using 'LIBNAME'

[SASuserlot]

Hi,

We have an excel file 'Spider_data.xlsx' on Server, where multiple people use this sheet to create dataset. Unfortunately some people reading the  sheet using 'LIBNAME' and PCFILE options which creating them a library causing the issue others to read this excel file throwing an error in the log  saying this file in use.

                      Problem is we are not sure who doing this to request delete the library or close the sheet. What are the ways to circumvent this issue other than creating another file and renaming or keep asking the people to check if they open the document ( where we hardly getting response back unless they are not aware of this issue).

Example of the code I am thinking but not sure this is what exactly..

Person 1  : code reading Excel sheet: guessing this person Causing the issue that other can not access the file.

libname dataf pcfiles path='c\spider_data.xlsx'
dbmax_text=20000;

data sp;
set dataf. 'sheet1$'n;
run;

Person 2 : Code reading excel sheet/ try importing directly:  ( This person getting the error in log that file already in use or somebody opened the source excel file)

PROC IMPORT OUT= WORK.sp1
DATAFILE= "c\spider_data.xlsx"
DBMS=EXCEL REPLACE;
RANGE="Sheet1$";
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

Thank you for your time.

[ballardw · Accepted solution]

---

@SASuserlot wrote:

Hi,

We have an excel file 'Spider_data.xlsx' on Server, where multiple people use this sheet to create dataset. Unfortunately some people reading the  sheet using 'LIBNAME' and PCFILE options which creating them a library causing the issue others to read this excel file throwing an error in the log  saying this file in use.

Thank you for your time.

---

Make it an organization wide standard when using a LIbname to 

1) establish the libname

2) copy the data from the library needed

3) clear the libname so the file is no longer locked.

Finding an enforcement mechanism may be difficult if you aren't using a server and admin tasks to limit access to files.

Or hire someone to go around the organization and beat folks that do not release file properly with wet spaghetti noodles.

[SASuserlot]

Thank you @ballardw .