Hi Team,
I want to get worksheet names from excel in order to validate the available sheets? since sometimes i did not get the required worksheets as part of excel and my sas codes starts throwing error for the same with proc import.
I tried below code for finding out the worksheet names available:
libname xls excel 'C:\test\testfile.xlsx';
data t;
set sashelp.vtable;
where libname = 'XLS';
run;
But continiously getting below error:
104 libname xls excel
104! 'C:\test\testfile.xlsx.xlsx';
ERROR: Connect: Class not registered
ERROR: Error in the LIBNAME statement.
105 data t;
106 set sashelp.vtable;
107 where libname = 'XLS';
108 run;
Can anyone help me on the same to resolve this error as I am stuck here!!
Why not solve the "problem" whch is that your using Excel. I would assum ethat you also don't have an import agreement - i.e. a document which details the strcuture and possibly contents of the data, which both parties have agreed to? If not, whatever you do is going to be hard to maintain, validate, use further. Simply put, Excel is not a good medium for "data".
Now there are no SAS functions to check Excel properties. you could use (version 9.4) libname excel. This can create a library reference to an XLSX file, and datasets witihin that library for each sheet. You could then take the information from sashelp.vtables where library=that libref.
However, this is all just trying to plaster up a poor data transfer process, and it will never work well.
As for your code:
libname xls excel 's:\temp\rob\a.xlsx'; data want; set sashelp.vtable; where libname="XLS"; run; libname xls clear;
The above worked fine for me. Are you using Sas 9.4? Is the file your accessing an actual XLSX file - not what the extension is called, I mean is it actually saved out of Excel as their native file, i.e. its not an XML file, or CSV or something else which can be processed by Excel. If the above are fine, make sure you have PC files access lilcensed, you also don't mention what operating system you are on.
Hi RW9,
Thanks for your quick help.
The file which i am trying to open is valid xlsx. I mean with valid xlsx extension.
I am currently using SAS 9.3 and i have SAS/ACCESS Interface to PC Files installed as copy of Proc setinit from my SAS PC as below:
Product expiration dates:
---Base SAS Software
31AUG2016
---SAS/STAT
31AUG2016
---SAS/GRAPH
31AUG2016
---SAS/ETS
31AUG2016
---SAS Enterprise Miner
31AUG2016
---SAS Text Miner
31AUG2016
---SAS Enterprise Guide
31AUG2016
---SAS/ACCESS Interface to PC Files
31AUG2016
---SAS Enterprise Miner for Desktop
31AUG2016
---SAS Workspace Server for Local Access
31AUG2016
---SAS Add-in for Microsoft Excel
31AUG2016
Regards,
Rajesh
Yes, libname excel is 9.4 only. You could have bitness problems, are you running on Windows? I still suspect the file isn't an XLSX file - right click on it and open in text pad, if it looks like XML then you don't have a valid XLSX file. A valid XLSX file is actually a ZIP file, you can change the .XLSX to .ZIP and then double click on it, you will see subdirectories and .XML files within.
Anyways, without knowing OS/BItness, why not do this the other way round. If your fixed on using Excel then just push out the information to a usable format from there. Excel has VBA with which you can write code - doesn't necessarily have to be in your data file - youo could have your own excel file with functions and run from there. Its a very simple task from there to write a text file with sheet names, or any metadata really:
Sub WriteNames() Open "c:\Sheets.txt" For Output As #1 For i = 1 To ActiveWorkbook.Sheets.Count Write #1, ActiveWorkbook.Sheets(i).Name Next i Close #1 End Sub
This assumes the activesheet is the one to export sheet names from.
Your error comes from either:
- you don't have SAS/ACCESS to PC Files licensed
- SAS/ACCESS was not correctly installed
- your SAS is not installed on Windows
- you have a bitness (64/32) problem between SAS and the installed MS Office
You might try to use the XLSX engine.
A much better solution is to switch from dungheap shuffling (aka "working with Excel") to transfer your data in a data transfer format like .CSV, fixed column text files, or XML.
Hi KurtBremser,
Thanks for your quick help.
The file which i am trying to open is valid xlsx. I mean with valid xlsx extension.
I tried with XLSX engine but got below error as:
libname xls xlsx "C:\test\testfile.xlsx";
ERROR: The XLSX engine cannot be found.
ERROR: Error in the LIBNAME statement.
I am currently using SAS 9.3 and i have SAS/ACCESS Interface to PC Files installed as copy of Proc setinit from my SAS PC as below:
Product expiration dates:
---Base SAS Software
31AUG2016
---SAS/STAT
31AUG2016
---SAS/GRAPH
31AUG2016
---SAS/ETS
31AUG2016
---SAS Enterprise Miner
31AUG2016
---SAS Text Miner
31AUG2016
---SAS Enterprise Guide
31AUG2016
---SAS/ACCESS Interface to PC Files
31AUG2016
---SAS Enterprise Miner for Desktop
31AUG2016
---SAS Workspace Server for Local Access
31AUG2016
---SAS Add-in for Microsoft Excel
31AUG2016
Regards,
Rajesh
I have Office 2013 and Yes, Libname is assigned in Window SAS .
Regarding, Checking the bits (SAS vs Office), what steps do i need to follow. Could you please guide me
Why, for $DEITY's sake, go through all the hassles?
Save the sheets as distinct .csv files, and import those. This is completely independent from SAS licensing and will work across ALL platforms/environments.
Excel native formats are no valid data transfer tool!
HI Reeza,
Thanks for your much needed help. Really appreciated.
So, it worked with your library decalration given as below:
Libname xls pcfiles path="path to excelfile";
I utilized them in my code as below :
libname xls pcfiles path= "C:\test\testfile.xlsx";
data t;
set sashelp.vtable;
where libname = 'XLS';
run;
Regards,
Rajesh
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: