BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hi all, has anyone had a problem with reading in an excel file created using the ods excelxp tagsets? When I try this:

proc import datafile="H:\APRIL 2007\Quick Recovery Report for APRIL 2007.xls"
out = tempQuickRec
dbms=excel replace;
sheet="Consolidated";
run;

I get the following error :

ERROR: Connect: External table is not in the expected format.
ERROR: Error in the LIBNAME statement.

If I replace the excel file with one created using excel it works fine. I'm running v9.1.3 sp3 with version 1.7 of the excelxp tagsets.

Thanks.
4 REPLIES 4
Cynthia_sas
SAS Super FREQ
Hi...
When you use TAGSETS.EXCELXP to create a file, you CAN use the following invocation:
[pre]
ods tagsets.excelxp file='c:\temp\myfile.xls';
...SAS code...
ods tagsets.excelxp close;
[/pre]

However, you are creating a Spreadsheet Markup Language XML file. You can prove this to yourself by opening the file -- created in the above code -- myfile.xls -- with Notepad or ANY text editor. What you should see at the top of the file (in Notepad) is this:
[pre]
<?xml version="1.0" encoding="windows-1252"?>

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
[/pre]

What does that mean? That means that the XML file is not a "true" binary Excel file -- it is just an XML file that Excel knows how to open. (Well, Excel "knows" how to open the XML because the XML conforms to the Microsoft spec -- that doesn't make it a "binary" Excel file -- no matter what the file extension is.)

PROC IMPORT is geared to read a "true" binary Excel file (a file created with Excel) -- NOT an XML file, such as the file created by the ExcelXP tagsets. If you write your ODS invocation as:
[pre]
ods tagsets.excelxp file='c:\temp\myfile2.xml';
...SAS code...
ods tagsets.excelxp close;
[/pre]

This is a more accurate representation of what the file is -- Spreadsheet Markup Language XML -- however -- the XML file extension does not automatically cause SAS to launch Excel for you when you click the icon in the Results window or for Windows Explorer to launch Excel for you when you click the icon in an Explorer window.

If you resave the XML file as an XLS file using FILE --> Save As in Excel, then you should be able to read it with PROC IMPORT.

However, since you used a SAS dataset to create the ExcelXP file, why not just use THAT SAS dataset instead of trying to re-import the ODS output file. I can imagine that, if your ODS output was the result of a summary procedure or an analysis procedure, that you might want to get that information from the procedure into Excel. You can create SAS datasets from procedures by using the ODS OUTPUT statement or by using the OUTPUT statement available in most SAS procedures.

For example, consider this program:
[pre]
ods tagsets.excelxp file='c:\temp\shoe_rept.xls' style=sasweb;

ods output onewayfreqs=work.freqout;
proc freq data=sashelp.shoes;
tables subsidiary;
run;
ods tagsets.excelxp close;
[/pre]

This program is creating 2 different files: shoe_rept.xls (really an XML file) AND the SAS data set WORK.FREQOUT -- which contains the results of the OneWayFreq in a SAS dataset.

I guess it all comes down to what you need to do and why you need to read the output from ExcelXP back into SAS.

cynthia
JoeT
Calcite | Level 5

In my case we will update the excel and we should import it back to sas. By chance is there any work around for this to import?

DataMatt
Calcite | Level 5

I have the same issue.  The only work around I found is to have the user make their updates to the exported XML file, use the Save As command in Excel to save it as a XLS or XLSX file, and send it back to me.

Cynthia_sas
SAS Super FREQ

Hi, you have responded to and reactivated a 7 year old posting. It would be much better for you to start a new post with your question and refer to this post. However, the answer is still the same. You don't need a "workaround", you need to adjust to the reality of using TAGSETS.EXCELXP. That ODS destination (TAGSETS.EXCELXP) does NOT, NOT, NOT create a true binary Excel file that can be imported. So your choices are:

1) create the XML file using TAGSETS.EXCELXP and manually or with a VBScript program save the file as proprietary .XLS or .XLSX

2) create the XML file using TAGSETS.EXCELXP and manually export the file to CSV format and read the CSV file back into SAS

3) create the XML file using TAGSETS.EXCELXP and use SAS XML Libname Engine with an XML Map to import the XML file back into SAS, as described in this paper http://www2.sas.com/proceedings/sugi31/115-31.pdf

cynthia

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 3237 views
  • 1 like
  • 4 in conversation