Friday, July 8, 2011

Reading Excel and Access files on SAS 64-bit

Finally got my machine rebuilt to Windows 7 64-bit, and had SAS 9.2.3 64-bit loaded.  Then I started to have problems running some SAS code to read Excel and Access files.  Error message is:
ERROR: Error trying to establish connection: Unable to create Data Source.: Class not
       registered
ERROR: Error in the LIBNAME statement.
The original SAS code for reading Excel and Access files are as follows:
libname xlsfile oledb init_string="Provider=Microsoft.ACE.OLEDB.12.0; Data source=c:\temp\ExcelFile.xlsx; extended Properties=Excel 12.0";

libname accfile oledb init_string="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=c:\temp\AccessFile.accdb; Persist Security Info=True";
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.

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";

libname accfile pcfiles type=Access port=8621 server=localhost path = "c:\temp\AccessFile.accdb";
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.

6 comments:

Corinna Lo said...

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.

Rob said...

Thanks for posting this! This has been a headache all day until I found this.

Shannon & Justin said...

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!

Mr. Aj said...

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

Corinna Lo said...

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.

jalles said...

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;