BookmarkSubscribeRSS Feed
wgfmoonpie
Calcite | Level 5

I've downloaded Excel files containing data for air monitoring stations on a yearly basis.  Therefore I'm looking at 20 or more Excel files per year from 1991 to 2015.  I've been able to easily import the Excel files from the later years using DBMS=xls.  However, when I get to those in 2007 they're save in an early Excel format.  Using DBMS=xls I get the indication that the file is Version 4 or early.  I then tried using the DBMS=excel and got a Class not found error.  Searching the internet I determined that I needed to install the PC FIle Server on my system as I'm running a 32 bit Windows and 64 bit SAS.  I downloaded the appropriate version based upon my SAS version and installed.  Using the following commands (note this is a portion of my macro which I know works as its what I've used in the later year runs): 

 

proc import datafile=&excelfilename
         out=temp1
         dbms=excelcs replace;
         sheet=&sheetname;
         run;

 

I end up with the following error messages:

 

ERROR: CLI describe error: [Microsoft][ODBC Excel Driver] External table is not in the expected format.
ERROR: Import unsuccessful.  See SAS Log for details.

 

I'm at a loss. I know I can open each excel file and convert it to a newer version of excel but that's more than 200 files. 

 

Any help would be most appreciated.

 

Thanks, WIlliam

5 REPLIES 5
Reeza
Super User

PC Files are for later versions not earlier versions. 

 

Try DBMS=Excel4 or Excel5

 

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

Kurt_Bremser
Super User

Hint for the future: do not store raw data in a Microsoft-derived format. Always use something that is easily readable across platforms and software generations and can be monitored with a simple text editor.

 

I'd use the opportunity and write a VBS that batch-converts all the data to .csv

wgfmoonpie
Calcite | Level 5

Definitely appreciate the responses.

 

I had tried 'Excel4' and just tried 'Excel5'.  Unfortunately both give the following message:

 

ERROR:  An exception has been encountered.
Please contact technical support and provide them with the following traceback information:

 

Kurt, I am in 100% agreement with you.  Unfortunately, this is data I had to download not data I had collected.  I therefore had no control of the format used, just trying to find the most effective way to get the data into a usable source.

 

Thanks again.

Reeza
Super User
Can you post a sample of the file? It may be faster to convert to cvs as Kurt suggested. Try it without sheet option.
Ksharp
Super User
What version is your SAS ? If it is 9.4 , also could try : dbms=xlsx

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 1954 views
  • 1 like
  • 4 in conversation