BookmarkSubscribeRSS Feed
Sandhya
Fluorite | Level 6
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.
3 REPLIES 3
RickM
Fluorite | Level 6
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
Cynthia_sas
SAS Super FREQ
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
Peter_C
Rhodochrosite | Level 12
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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2111 views
  • 0 likes
  • 4 in conversation