Good morning folks – I need your advice.
I have a macro that reads a single worksheet out of a group of Excel files that change both in number and content on a regular basis.
Historically, these files have been located on a shared drive. When our laboratory document management system became available, I moved the files in there because they were accessible by the same filename using a UNC path.
Unfortunately, the document management system was recently changed, and direct access is now by URL. To make things more complicated, the files are now stored as a section title and number that has no extension (something like LAB_000000000). The original filename is stored in an Oracle table I can see, so I can get the storage name (dDocName).
I have successfully used a FILENAME URL statement to read a .csv file from the new system, but have not able to find any documentation or examples of FILENAME with LIBNAME when reading an Excel file under Windows. I am running a remote session to a server with SAS 9.2 (TS2M0), and SAS/ACCESS for Oracle and PC File Formats.
To see a single Excel file in our previous document management system, I used the following:
LIBNAME GETIT EXCEL "\\dmsprod2\labs\Project Manager\PM_lightfile_001.xlsx" access=readonly ;
DATA README ;
SET GETIT.'FINAL$'n ;
RUN ;
LIBNAME GETIT CLEAR ; RUN ;
In the new document management system, the file PM_lightfile_001.xlsx corresponds to something like this:
http://ecm.ouragency.com/ecm/idcplg?IdcService=GET_FILE&RevisionSelectionMethod=Latest&dDocName=LAB_...
However, I could not get past the extension problem to tell if the URL was a problem or not.
I then went back to the original drive method, and established copies of the same Excel file with and without the .xlsx extension. The version without the extension does not work (log below).
Does anyone have experience with this, or could you please point me to an example or two? Going back to text files is not an option, and I would prefer not to pull all these files back out of the document management system onto a drive.
Thanks for any advice or help you can give me.
Wendy T.
19 %LET NAMO = &data_req.\PM_lightfile_001.xlsx ;
20 libname GETIT excel "&NAMO" access=readonly ;
NOTE: Libref GETIT was successfully assigned as follows:
Engine: EXCEL
Physical Name: \\palfile\sjrx\ES\IRL\IRLDB\data_req\PM_lightfile_001.xlsx
21 DATA README ;
22 SET GETIT.'FINAL$'n ;
23 RUN ;
NOTE: There were 208 observations read from the data set GETIT.'FINAL$'n.
NOTE: The data set WORK.README has 208 observations and 19 variables.
NOTE: DATA statement used (Total process time):
real time 0.13 seconds
cpu time 0.10 seconds
24 LIBNAME GETIT CLEAR ;
NOTE: Libref GETIT has been deassigned.
24 ! RUN ;
25 %LET NAMO = &data_req.\PM_lightfile_001 ;
26 libname GETIT excel "&NAMO" access=readonly ;
ERROR: ERROR: File extension specified is invalid.
ERROR: Error in the LIBNAME statement.
27 DATA README ;
28 SET GETIT.'FINAL$'n ;
ERROR: Libname GETIT is not assigned.
29 RUN ;
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.README may be incomplete. When this step was
stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.README was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds
30 LIBNAME GETIT CLEAR ;
WARNING: Libname GETIT is not assigned.
30 ! RUN ;