When you use SAS 9.2 to import or export Microsoft Excel or Microsoft Access files in the Windows x64, Windows Vista 64, and Windows 2003 64-bit server environments, you can receive the following message:
ERROR: DBMS type EXCEL (ACCESS) not valid for import.
A similar message for export can occur when exporting. Several other engines display the same message including EXCEL97 and EXCEL2000.
In addition, when you use the Import and Export wizards, the Excel engine is not presented as a selection.
For the operating environments listed previously, SAS 9.2 is a 64-bit release, while SAS 9.1.3 is a 32-bit release for these environments. The problem occurs because SAS 9.2 is a 64-bit release, but the Excel engine (DBMS=EXCEL) is a 32-bit engine. The XLS engine and the EXCELCS engine are 64-bit engines, so they work on 64-bit systems.
Depending on the requirements of the programs that you are running, you can solve this problem in one of two ways, as explained in the following sections.
Option 1
You can continue to use the 64-bit version of SAS, which can access more memory (greater than 4 gigabytes) than the 32-bit version of SAS. In this case, you need to use 64-bit import engines (XLS, EXCELCS, and ACCESSCS) to import your PC files, as illustrated by the examples that follow later in this section.
The XLS engine does not require the SAS® PC Files Server, but the engine cannot read Microsoft Office 2007 files (XLSX, XLSM, and XLSB). In addition, the XLS engine cannot be used with a LIBNAME statement. This engine can be used only in the IMPORT and EXPORT procedures, as shown here:
proc import out=mysasdata
datafile="drive-name:\directory\filename.xls"
dbms=xls replace;
sheet='sheet-name';
run;
proc export data=mysasdata
outfile="drive-name:\directory\filename.xls"
dbms=xls replace;
sheet='sheet-name';
run;
quit;
The EXCELCS engine does require the SAS PC Files Server, and it can read Microsoft Office 2007 files (XLSX, XLSM, and XLSB). This engine can also be used with a LIBNAME statement that contains the PCFILES engine. The following examples illustrate the use of the EXCELCS engine:
proc import dbms=excelcs
datafile='\directory\file.xls'
out=sas-data-output-filename replace;
sheet='sheet-name';
server="server-name.company.com";
port=8621;
run;
proc export dbms=excelcs data=mysasdata
outfile="\directory\filename.xls"
replace;
sheet='sheet-name';
server="server-name.company.com";
port=8621;
run;
You can also access Excel files using the PCFILES engine in a LIBNAME statement, as shown in this example:
libname myref pcfiles server="server-name.company.com"
port=8621 path="\directory\filename.xls";
For Microsoft Access files, you must use the SAS PC Files Server, as shown in these examples:
proc import dbms=accesscs
out=sas-data-output-filename replace
table='accessmdb-table-name';
port=8621;
server="server-name.company.com";
database='\directory\filename.mdb';
run;
proc export data=sashelp.shoes outtable='accessmdb-table-name'
dbms=accesscs replace;
database='\directory\filename.mdb';
server="server-name.company.com";
port=8621;
run;
You can also access Microsoft Access files using the PCFILES engine in a LIBNAME statement, as shown in this example:
libname myref pcfiles server="server-name.company.com"
port=8621 path="directory\filename.mdb";
The SAS PC Files Server is not required in order to use XLS engine, but the XLS engine cannot read Access files.
To use the EXCELCS and ACCESSCS engines, you must install the SAS PC Files Server. For details about how to install and use the SAS PC Files Server as well as for information about how other formats work, see the following documentation:
Option 2
You can leave the 64-bit version of SAS installed and also install the 32-bit version of the SAS® System for Windows so that PROC IMPORT and PROC EXPORT work with Excel and Access files the same as they do in SAS 9.1.3. However, the 32-bit version is limited to 4 gigabytes of memory. Both versions can be installed on the same machine and do not interfere with each other.
Operating System and Release Information
| SAS System | SAS/ACCESS Interface to PC Files | Microsoft Windows XP 64-bit Edition | 9.2 TS1M0 | |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
