Reading an XLS file generated with ExcelXP Tagset

[deleted_user]

We have recently converted from PC SAS to Enterprise Guide on an AIX platflorm and are in the process of converting alot of production code utilizing DDE to ODS ExcelXP tagsets.

One request I am having a problem with generates a report with ExcelXP using style minimal and creates the report with the XLS extension. One of our clinical people updates the spreadsheet. This is a daily report. After the spreadsheet is updated, it has to be imported back into SAS and then updated again the next day.

I FTP the .XLS file over to AIX and then use proc import to convert the file to a SAS Dataset. This will not work. I receive an error that says the file is not OLE.

Is there a way to read this file in without manual intervention on the Windows side?

[Cynthia_sas]

Hi,
TAGSETS.EXCELXP generates a Microsoft Spreadsheet Markup Language XML file. Partly, what you need to do depends on whether your person is saving the spreadsheet as XML or saving the spreadsheet as XLS format.

The Excel default when you do a FILE --> SAVE is to save the file as the same file type (which would be XML). You cannot read the Spreadsheet Markup Language XML with PROC IMPORT directly. You might consider asking the person who's doing the updates to do a FILE --> SAVE AS and to explicitly select the pulldown file type of .XLS and explicitly save the file as a "true Excel XLS" file, instead of an XML file. Proc IMPORT will only read the specific file types that it "knows" about -- CSV, XLS, Lotus, DBF, etc. And, SAS will only read XLS files if you have SAS/Access for PC File Formats (PCFF). On Unix, if you do not have PCFF, I don't think you get the Excel "reader" for PROC IMPORT. (This is a question for Tech Support -- some licenses include PCFF; for some licenses, it used to be an extra add-in -- they'd have to check your particular setup.).

To read XML into SAS data set form, if the data follows a regular structure, then you can use the SAS XML libname engine to read XML into SAS format. However, the Microsoft SpreadsheetML does not follow the kind of "regular" conventions that the XML libname engine expects. So, in that case, you would need to use the SAS XML Mapper to create a MAP from the XML form to the SAS data set form.

If you do decide to go down the SAS XML Libname engine road with XML Mapper, then you'll have to contact Tech Support for help with the Libname Engine and XML Mapper syntax. This paper describes the round trip process using XML, however, you would have to follow the instructions in the paper and download the SAS XML Map and the macro programs described in the paper in order for the round trip to work: http://support.sas.com/rnd/papers/sugi29/ExcelXML.pdf

To find out how to contact Tech Support, refer to:
http://support.sas.com/techsup/contact/index.html

cynthia