Behind the Door Marked ‘Beware of the Leopard’: Importing Excel 2007 into SAS 9.2 on Windows 7 x64

Some times documentation can be a little hard to find…

You may be aware of the fact that, if you are running SAS 9.2 on a 64-bit Vista or Windows 7 machine the Import Data option from the file menu does not work for Excel files.

Per SAS Usage Note 33228: (Courtesy of Peter Ruzsa in SAS Technical Support.)
You are running into this issue here,

“An error occurs when you use SAS® 9.2 to import or export Microsoft Excel or Access files in the Windows x64 and Windows Vista 64 environments.”
(Yes, we know that.)

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.
In addition, when you use the Import and Export wizards, the Excel engine is not presented as a selection.

(Yes, and this makes us sad because people insist on continuing to email us files in Excel format, and Access, too, but we have these shiny new computers running SAS 9.2 that we want to use and, on top of it all, we are out of doughnuts. They keep buying that raspberry arugala crap instead. Why do we always modernize the wrong things?)

You could save your Excel 2007 files as .csv and import them that way but that is pretty inefficient.

So, let’s read on in Pete’s note… well, actually, let’s not because it had some code in it that probably works for some people in certain situations. I was not one of those people. However, maybe you are, so you can go to the SAS knowledge base and read it here.

http://support.sas.com/kb/33/228.html

When that didn’t work, I tried swearing. Next, I went to the documentation for PC Files Server, specifically, this page

http://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#/documentation/cdl/en/acpcref/63184/HTML/default/a003353773.htm

which gives the exact correct code for running Proc Import, assuming you have the PC Files Server installed. Which, it turns out, I did not.

So …, from a different helpful person at Tech Support, I received the following:

“Note if you have an existing 9.1 or 9.2 pc file server you should uninstall it first.

1. Download the PC file server from the following location to your windows pc that
is going to run the application. You can find it at this location:

ftp://ftp.sas.com/techsup/download/base/zqjpcfileserver92m3.zip

you can simply save the file to any location on the pc where you are going to install the SAS PC File Server

2. For more information on the PC file server go to this link here.

http://support.sas.com/documentation/cdl/en/acpcref/61891/HTML/default/a002645029.htm

3. Unzip the zqjpcfileserver92M3.zip file on your pc, it will unzip to
the pcfilesrv__92130__prt__xx__sp0__1 sub directory where you stored the zip file.
4. In the unzipped directory named pcfilesrv__92130__prt__xx__sp0__1 double click on
the setup.exe
5. This will start the install

a. The setup.exe will install the pc file server in the C:\Program Files\SAS\PCFilesServer\9.2 directory.
If you are installing this on an X64 box it will install in C:\Program Files (x86)\SAS\PCFilesServer\9.2 directory because
this is a 32 bit application.
b. You will have a choice to install the pc file server as a service. The checkbox selection is

Start Service Now and When Windows Starts.

c. Note that if you install it as a service you must read network drive names with their Universal Naming Convention names such
as \\servername\directory\filename.xls.

After I installed the PC Files server, everything worked absolutely lovely to import Excel files, whether using the Import Data option in the File menu or Proc Import in my code ON WINDOWS 7 x64. So, my advice is that if you have a shiny new computer and a shiny new SAS 9.2 Maintenance 3 and you want to import the latest in Excel files or Access, download and install the PC Files server and you will be happy. Someone might even bring you doughnuts. But don’t count on that.

When I tried the same exact steps in Vista 64 I received a message. “Connection failed. See log for details.” The “details” were that SAS stopped processing this step because of errors.

Bad computer! No doughnut !