11-02-2016 06:42 AM
Is there a way to find if an excel sheet which is to be imported into sas is kept open while running the importing code in sas? If so , could you kindly let me know how is it done.(I don't want to look into the log) I need to identify if the excel is open or closed in SAS which its being imported. My case,I need sas to create a new variable "Check" beside the excel name (Which is to be imported) in an output excel sheet and place the variable values as "Open" if the excel is open. and "Close" as if the excel is closed while importing.
I am using SASEG 7.1
Help is appreciated
11-02-2016 07:01 AM - edited 11-02-2016 07:15 AM
Sounds like you have a real mess of a process there. Why do you need to be importing/exporting Excel files like? As for your question, depends on what you are doing, if you libname to the file, then the file is locked until the libname is removed. If it is a proc import then it should only take a short amount of time, but I suspect it will be locked during that time, its never really a question that comes up. Personally I avoid using Excel for anything as much as possible, however in some rare occurences it is not possible to avoid - in these scenarios minimise the use of either SAS or Excel, i.e. if your doing a lot of work in Excel, then use VBA, drop SAS, if most is in SAS, then just import the data and forget Excel.
Just to add, when Excel opens a file it creates a hidden file which tells it whether it is locked or not. If you open folder options and switch Show hidden files and folders on, you will see them, called ~$<filename>. Not sure if this is true of the import function however.
11-02-2016 05:57 PM
What you are trying to do probably wont work. While your SAS job is reading the Excel file it will be locked and unable to be updated until the import has finished. You can't simultaneously read an Excel workbook and update another sheet in the same workbook either with the same SAS process (SAS processes are sequential - one must follow the other) or a different SAS process.