ERROR: Error trying to establish connection: Unable to create Data Source.: Class notThe original SAS code for reading Excel and Access files are as follows:
registered
ERROR: Error in the LIBNAME statement.
libname xlsfile oledb init_string="Provider=Microsoft.ACE.OLEDB.12.0; Data source=c:\temp\ExcelFile.xlsx; extended Properties=Excel 12.0";Working with SAS technical support, I found that our tech guy installed Office 32-bit on my machine. SAS 64-bit could not use the 32-bit Office drivers. But what I understand from technical support was even if I had Office 64-bit installed, I still would encounter some issues because 64-bit is officially supported in SAS version 9.3. They mentioned something called PC Server that could help me. I read up about it online, and eventually found a solution.
libname accfile oledb init_string="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=c:\temp\AccessFile.accdb; Persist Security Info=True";
First, I have to install SAS PC Server component on my machine. Then I need to change the SAS code to the followings accordingly:
libname xlsfile pcfiles type=Excel port=8621 server=localhost path = "c:\temp\ExcelFile.xlsx";Thanks to this article DID YOU KNOW THAT YOU CAN NOW ACCESS PC FILES ON A WINDOWS MACHINE DIRECTLY FROM A UNIX MACHINE? which helped me solve the problem.
libname accfile pcfiles type=Access port=8621 server=localhost path = "c:\temp\AccessFile.accdb";
6 comments:
I added this code to check if the machine is 64-bit or not first, and then load the data:
* --- determine if this program is running on Vista x64 or Windows 7 x64 ---;
%macro detect_64bit_platform();
%global sas64;
%if &sysscpl=X64_VSPRO %then %let sas64=1;
%else %let sas64=0;
%mend detect_64bit_platform;
%detect_64bit_platform;
%put &sas64;
* --- load data from Excel spreadsheet ---;
%macro deflib_xlsfile;
%if &sas64=1 %then %do;
libname xlsfile pcfiles type=Excel port=8621 server=localhost
path = "c:\temp\ExcelFile.xlsx";
%end;
%else %do;
libname xlsfile oledb
init_string="Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=c:\temp\ExcelFile.xlsx;
Persist Security Info=True";
%end;
%mend deflib_xlsfile;
%deflib_xlsfile;
data xlsdata;
set xlsfile."Sheet1$"n;
run;
libname xlsfile clear;
This is useful if you will be sharing code between your machine and a colleague's 32-bit machine.
Thanks for posting this! This has been a headache all day until I found this.
Thank you for posting this information. I needed to know how to create a libname statement to connect to an Access 2010 database. This completely answered by question!
hi Corninna,
i am to see this macro used to connect the excel file but i am new bee and little knowledge to use macro ,
As i try to find other solution without using the macro .
the simple way is using this syntax
libname test pcfiles path=' path excel file ';
it is also connect the respective sheets same as normal when saw the error mention above.
thank for your excel connect macro please also advice how i will use this macro to read excel file .
i tried but less knowlege of macro make me trouble , can you publish a compleast macro so that we just put excel file location and read all sheet of the excel file .
thank for this great topic .
MAD
I added the macro because I often need to share my SAS program with my colleagues. Some of them were still using 32-bit machines at the time. Therefore, I added the macro so SAS will detect if the machine is running 64-bit operating system. If yes, then use this pc server approach to open the Excel file. Otherwise, use the old OLEDB approach.
BTW, starting with SAS 9.3, I notice the port number is changed from 8621 to 9621.
There's a easy way doing that:
Hi, you can solve your problem ease.
Try use this routine and have fun:
PROC IMPORT OUT= WORK.name_your_DB
DATAFILE= "C:\your_file.xls"
DBMS=EXCELCS REPLACE;
RANGE="name_of_the_worksheet_tab";
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
Post a Comment