Excel --> XML --> SAS

[Scott Bass]

Hi,

I did search the archives before posting, and found a zillion hits for
exporting SAS to Excel, but few for importing Excel into SAS (and no hits
using Excel XML files).

Problem: Customer has a number of Excel spreadsheets that need to get
imported into SAS datasets. The structure of these spreadsheets can
change often. I'm hoping to minimize the impact of these structural
changes on the import program (such as column order). Assume row 1
contains column names. Finally, SAS data tier is on Unix.

I was hoping XML might help address this issue (somewhat). So, does
anyone have an example of saving an Excel spreadsheet as XML, then a SAS
program to import that XML into a SAS dataset? Sample code greatly
appreciated if you have some.

Alan???

Thanks,
Scott

[Alan Churchill]

Scott,

Your best bet, IMO, is to use OleDB to read the sheets or bail out and use a
product like Aspose to read in the sheets and then write them out to SAS.

XPath is your friend here. SAS does not support it however so you will need
to find a 3rd party alternative IMO. XPath will make your life much, much
easier in this regards.

Alan

Alan Churchill
Savian "Bridging SAS and Microsoft Technologies"
www.savian.net



-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of Scott
Bass
Sent: Friday, November 10, 2006 9:23 PM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Excel --> XML --> SAS

Hi,

I did search the archives before posting, and found a zillion hits for
exporting SAS to Excel, but few for importing Excel into SAS (and no hits
using Excel XML files).

Problem: Customer has a number of Excel spreadsheets that need to get
imported into SAS datasets. The structure of these spreadsheets can
change often. I'm hoping to minimize the impact of these structural
changes on the import program (such as column order). Assume row 1
contains column names. Finally, SAS data tier is on Unix.

I was hoping XML might help address this issue (somewhat). So, does
anyone have an example of saving an Excel spreadsheet as XML, then a SAS
program to import that XML into a SAS dataset? Sample code greatly
appreciated if you have some.

Alan???

Thanks,
Scott

[Don Henderson]

You might also want to take a look at:

http://support.sas.com/news/feature/2006/workbooks.html

On the right side of the page, there is a link that will take you to a
page where you can get a copy of a paper and a ZIP archive that contains
sample code and data that uses XML to create and/or import Excel workbooks
into SAS.

Post by Alan Churchill
Scott,
Your best bet, IMO, is to use OleDB to read the sheets or bail out and

use a

Post by Alan Churchill
product like Aspose to read in the sheets and then write them out to SAS.
XPath is your friend here. SAS does not support it however so you will

need

Post by Alan Churchill
to find a 3rd party alternative IMO. XPath will make your life much, much
easier in this regards.
Alan
Alan Churchill
Savian "Bridging SAS and Microsoft Technologies"
www.savian.net
-----Original Message-----
Bass
Sent: Friday, November 10, 2006 9:23 PM
Subject: Excel --> XML --> SAS
Hi,
I did search the archives before posting, and found a zillion hits for
exporting SAS to Excel, but few for importing Excel into SAS (and no hits
using Excel XML files).
Problem: Customer has a number of Excel spreadsheets that need to get
imported into SAS datasets. The structure of these spreadsheets can
change often. I'm hoping to minimize the impact of these structural
changes on the import program (such as column order). Assume row 1
contains column names. Finally, SAS data tier is on Unix.
I was hoping XML might help address this issue (somewhat). So, does
anyone have an example of saving an Excel spreadsheet as XML, then a SAS
program to import that XML into a SAS dataset? Sample code greatly
appreciated if you have some.
Alan???
Thanks,
Scott

[Hoyle, Larry]

A great SUGI 31 paper on this is Vincent DelGobbo's (paper 115-31), =
"Creating AND Importing Multi-Sheet Excel Workbooks the Easy Way with =
SAS"
=20
You can find the paper online at:
http://www2.sas.com/proceedings/sugi31/115-31.pdf


=20