* SOAP0013 My goal Send SAS tables ( data/metadata only ) to other Windows applications and create SAS tables from other Windows Applications. I do not care about Graphs/Animations/Forms/Reports. I do not care about UNIX, MVS/370 or OS/390(POSIX?) ie SAS Table -> Lotus/QuatroPro/MS-Excel/Ms-Access/MS-Visio(Metadata), MS-Foxpro/MS-Sql-Server/MS-Word/Wordperfect/MS-Powerpoint, Sybase Power Designer(Metadata empty tables), ERWIN, Rational Rose etc MS-Access/MS-Sql-Server/MS-Foxpro/Subase Power Designer -> SAS Table Rational Rose/MS-Visio/ERWIN My initial impressions of XML as a technique to export and import database tables between Microsoft products and SAS is mixed. Although I could move very simple tables back and forth between SAS and MS-Access, Microsoft seems to be able to easily move very complex tables among it products. ie ( MS-Visio, MS-FoxPro, MS-Sql-Server, MS-Access , MS etc etc ) /*------------------------------------------------------------*\ | What I tried | \*------------------------------------------------------------*/ SAS V8.2 XML --> MS_Access 2002 ( Office XP ) MS_Access 2002(XP) --> SAS V8.2 I installed Office XP and tried to move data tables back and forth between Access and SAS. /*------------------------------------------------------------*\ | I was able to move data from MS-Access to SAS | \*------------------------------------------------------------*/ I was able to move very simple tables back and forth. I did have problems with more complex Access tables. Tables exported from Access as XML could be imported back into Access and the tables were exact images of the original tables, with indexes, autonumbers etc. The XSL and XSD contained detail data and a Visual Basic Script to configure the table. The XSL had over 400 lines of code. I was unable to get SAS to recognize the XSL and/or XSD files that MS-Access created. I hope SAS will quickly use this information. I expect the Access created XML can be imported easily into MS-SQL-Server, MS-Excel, MSFoxPro, MS-Visio etc. /*------------------------------------------------------------*\ | I was able to move data from SAS to MS-Access | \*------------------------------------------------------------*/ * Creating an XML Table ??? ; libname rss xml "e:\utl\simpletable.xml"; data rss.simpletable; DO row='A','B','C'; Col1='COL'!!Row; Col2='COL'!!Row; Col3='COL'!!Row; Output; END; RUN; The CONTENTS Procedure Data Set Name: RSS.SIMPLETABLE Observations: . Member Type: DATA Variables: 4 Engine: XML Indexes: 0 Created: . Observation Length: 0 Last Modified: . Deleted Observations: 0 Protection: Compressed: NO Data Set Type: Sorted: NO Label: -----Alphabetic List of Variables and Attributes----- # Variable Type Len Pos Format Informat Label ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ 3 COL1 Char 5 16 $5. $5. COL1 2 COL2 Char 5 8 $5. $5. COL2 1 COL3 Char 5 0 $5. $5. COL3 4 ROW Char 2 24 $2. $2. ROW -----Variables Ordered by Position----- # Variable Type Len Pos Format Informat Label ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ 1 COL3 Char 5 0 $5. $5. COL3 2 COL2 Char 5 8 $5. $5. COL2 3 COL1 Char 5 16 $5. $5. COL1 4 ROW Char 2 24 $2. $2. ROW The XML Document - - A COLA COLA COLA - B COLB COLB COLB - C COLC COLC COLC
When I imported the XML table into Access it looked like Field Name Data Type Length Description ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ 1 ROW TEXT 255 2 COL1 TEXT 255 3 COL2 TEXT 255 4 COL3 TEXT 255 The fields(columns did have the right data (typed incorrectly) */ /*------------------------------------------------------------*\ | I was able to move data from MS-Access to SAS | \*------------------------------------------------------------*/ /* Using the Export Option in MS-Access The (Nortwinds) Shipper table in Access looks like Field Name Data Type Length Description ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ 1 ShipperID LongInt 2 CompanyName Text 40 3 Phone Text 24 * XML created by access ; 1 Speedy Express (503) 555-9831 2 United Package (503) 555-3199 3 Federal Shipping (503) 555-9931 * Access also created an XSL and XSD documents ( Several hundred lines ) * here is the SAS code to import the Access Table libname rss xml "shippers.xml"; proc contents data=rss.shippers; run; The CONTENTS Procedure (SAS Type XML Table Typed incorrectly ); Data Set Name: RSS.SHIPPERS Observations: . Member Type: DATA Variables: 3 Engine: XML Indexes: 0 Created: . Observation Length: 0 Last Modified: . Deleted Observations: 0 Protection: Compressed: NO Data Set Type: Sorted: NO Label: -----Alphabetic List of Variables and Attributes----- # Variable Type Len Pos Format Informat Label ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ 2 COMPANYNAME Char 16 16 $16. $16. COMPANYNAME 1 PHONE Char 14 0 $14. $14. PHONE 3 SHIPPERID Num 8 32 BEST8. BEST8. SHIPPERID ; * SAS Table from type XML Simple datastep data shippers; set rss.shippers; run; Data Set Name: WORK.SHIPPERS Observations: 3 Member Type: DATA Variables: 3 Engine: V8 Indexes: 0 Created: 11:11 Monday, June 25, 2001 Observation Length: 40 Last Modified: 11:11 Monday, June 25, 2001 Deleted Observations: 0 Protection: Compressed: NO Data Set Type: Sorted: NO Label: -----Engine/Host Dependent Information----- Data Set Page Size: 4096 Number of Data Set Pages: 1 First Data Page: 1 Max Obs per Page: 101 Obs in First Data Page: 3 Number of Data Set Repairs: 0 File Name: c:\wrk\_TD992\shippers.sas7bdat Release Created: 8.0202M0 Host Created: WIN_PRO -----Alphabetic List of Variables and Attributes----- # Variable Type Len Pos Format Informat Label ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ 2 COMPANYNAME Char 16 22 $16. $16. COMPANYNAME 1 PHONE Char 14 8 $14. $14. PHONE 3 SHIPPERID Num 8 0 BEST8. BEST8. SHIPPERID