topic converting excel file to SAS dataset using xlxp2sas in SAS Programming

converting excel file to SAS dataset using xlxp2sas

maggi2410 — Thu, 29 Nov 2012 23:21:03 GMT

Hi All,

I am converting an excel file to SAS dataset using xlxp2sas. The reason I am using xlxp2sas is:

My excel file has columns with text more then 250 characters and I am using UNIX(9.2) system, and the sheet name has special characters, and the header names has special characters ..phew!!

the excel libname statement doesnt work on UNIX, and Proc import causes truncation. In short, I have to use xlxp2sas.

Nowwhen I use it on test file(with 1 row, 1 column), even throws error "ERROR: Expected an XML document prolog or element.", even on test file.

I am using the attached map file.

Do anyone has any idea to resolve this pesky error???

My code is :

%xlxp2sas(excelfile=./test.xls,

mapfile=./excelxp.map);

Re: converting excel file to SAS dataset using xlxp2sas

maggi2410 — Thu, 29 Nov 2012 23:21:31 GMT

This is a simple excel file, 2003, not an xml file

Re: converting excel file to SAS dataset using xlxp2sas

Cynthia_sas — Fri, 30 Nov 2012 04:19:59 GMT

Hi:

I believe that program ONLY works on Excel XML as input: http://www2.sas.com/proceedings/sugi30/136-30.pdf and would not work on an Excel 2003 non-XML format file. You might want to check with Tech Support to be absolutely sure, but when I look at the paper that describes the program,I don't see any references to using it on proprietary Excel format files. The paper author includes the steps for how to explicitly save your Excel file as XML in order to use the macro program to read the Excel XML file into SAS.

cynthia