topic Reading an excel file in SAS Procedures

Reading an excel file

Sandhya — Tue, 11 May 2010 19:31:40 GMT

Hi,

I've created an excel file using the following command in SAS 9.2
ods tagsets.excelxp file="round.xls"
style=sasweb
options(embedded_titles='yes'
embedded_footnotes='yes'
sheet_interval='none'
sheet_label=" ");

Now I wanted to imported this round.xls file to work.round in SAS. I tried the following code

proc import
datafile = 'round.xls'
out = work.round
dbms = xls
replace;
getnames=yes;
run;

I get the following error.

File is not OLE2
Requested Input File Is Invalid
ERROR: Import unsuccessful. See SAS Log for details.

I tried different names for the input files.

Please help.

Sandhya.

Re: Reading an excel file

RickM — Tue, 11 May 2010 19:50:17 GMT

My guess would be that this happens because the file you create is really a html (or is it xml?) file. From what I have read the .xls just makes it so Excel is the default program to open the file. Try opening the file, save it as an Excel file, and then try your proc import to see if this is correct (I'm no expert so it may be wrong). Message was edited by: RickM

Re: Reading an excel file

Cynthia_sas — Tue, 11 May 2010 22:42:38 GMT

Rick:
You are exactly correct. The file created by TAGSETS.EXCELXP is a Microsoft Office Spreadsheet Markup Language XML file that conforms to the Office 2002/2003 specification for XML description of a workbook/spreadsheet.

When someone uses .XLS as a file extension in an ODS TAGSETS.EXCELXP program, it is merely a convenience -- so Windows will launch Excel when they double click on a file with .XLS, Excel launches (instead of the browser, which is the default file open program for an XML file).

ODS TAGSETS.EXCELXP does NOT create a "true, binary" .XLS file -- so your advice to open the file in Excel and do a SAVE AS -->XLS (not just a SAVE -- which will save to XML format) -- was good advice. It is the only way to ensure that the file is a binary .XLS file.

(Also, if someone has Office 2007 and saves to the new .XLSX format, they can only read that file format with SAS using SAS 9.2 Phase 2 and Proc Import or the Excel Libname Engine).

cynthia

Re: Reading an excel file

Peter_C — Thu, 13 May 2010 13:35:22 GMT

one way to read a file created in microsoft spreadsheet mark-up language - (like these created by ODS tagsets.excelXP )
- - - - - - - - -> use sas xml-Mapper
The version in SAS9.2 provides an "autoMap" feature that does most of the work. Then a bit of sql can de-normalise the tables (which starts looking like a database in 3rd normal form !) and it will provide you with the equivalent of the data sheets in plain CSV text

good luck.

PeterC