%LET xlfile=c:\temp\mappe2.xml; /* zu lesende XML-Datei */
%LET out=aus;                   /* Ausgabedatei */
%LET maxDataLength=100;         /* Maximal zu lesende Länge eines Zelleninhalts */
%LET maxCommentLength=100;      /* Maximal zu lesende Länge eines Kommentars */

/*-- XML-Map-Datei erzeugen ---------------------------------------------------*/
DATA _null_;
   INFILE DATALINES TRUNCOVER;
   FILE "%sysfunc(pathname(work))/xlmap.xml";
   INPUT line $char128.;
   line = resolve(line);
   l = length(line);
   PUT line $varying. l;
DATALINES;
<?xml version="1.0" ?>
<SXLEMAP version="1.2">
   <TABLE name="cells"> 
      <TABLE-PATH syntax="xpath"> 
         /Workbook/Worksheet/Table/Row/Cell
      </TABLE-PATH>

      <COLUMN name="SheetCount" ordinal="YES"> 
         <INCREMENT-PATH syntax="XPath">
            /Workbook/Worksheet
         </INCREMENT-PATH>
         <TYPE>numeric</TYPE>
         <DATATYPE>integer</DATATYPE>
      </COLUMN>

      <COLUMN name="SheetName" retain="YES"> 
         <PATH> 
            /Workbook/Worksheet/@ss:Name
         </PATH>
         <TYPE>character</TYPE>
         <DATATYPE>string</DATATYPE>
         <LENGTH>31</LENGTH>
      </COLUMN>

      <COLUMN name="RowCount" ordinal="YES"> 
         <RESET-PATH syntax="XPath">
            /Workbook/Worksheet/Table
         </RESET-PATH>
         <INCREMENT-PATH syntax="XPath">
            /Workbook/Worksheet/Table/Row
         </INCREMENT-PATH>
         <TYPE>numeric</TYPE>
         <DATATYPE>integer</DATATYPE>
      </COLUMN>

      <COLUMN name="RowIndex"> 
         <PATH> 
            /Workbook/Worksheet/Table/Row/@ss:Index
         </PATH>
         <TYPE>numeric</TYPE>
         <DATATYPE>integer</DATATYPE>
      </COLUMN>

      <COLUMN name="ColumnIndex"> 
         <PATH> 
            /Workbook/Worksheet/Table/Row/Cell/@ss:Index
         </PATH>
         <TYPE>numeric</TYPE>
         <DATATYPE>integer</DATATYPE>
      </COLUMN>

      <COLUMN name="DataType"> 
         <PATH> 
            /Workbook/Worksheet/Table/Row/Cell/Data/@ss:Type
         </PATH>
         <TYPE>character</TYPE>
         <DATATYPE>string</DATATYPE>
         <LENGTH>12</LENGTH>
      </COLUMN>

      <COLUMN name="Data"> 
         <PATH> 
            /Workbook/Worksheet/Table/Row/Cell/Data
         </PATH>
         <TYPE>character</TYPE>
         <DATATYPE>string</DATATYPE>
         <LENGTH>&maxDataLength</LENGTH>
      </COLUMN>

      <COLUMN name="Comment"> 
         <PATH> 
            /Workbook/Worksheet/Table/Row/Cell/Comment/ss:Data/Font
         </PATH>
         <TYPE>character</TYPE>
         <DATATYPE>string</DATATYPE>
         <LENGTH>&maxCommentLength</LENGTH>
      </COLUMN>

   </TABLE>
</SXLEMAP>
;

/*-- Libref definieren, das mit Hilfe der XML-Map die XML-Datei liest ---------*/
LIBNAME _xlxml XML "&xlfile" XMLMAP="%sysfunc(pathname(work))/xlmap.xml";

/*-- Maximale Längen der Felder bestimmen -------------------------------------*/
PROC SQL NOPRINT;
   SELECT 
      max(length(data))
     ,max(length(comment)) 
   INTO 
      :dataLength
     ,:commentLength
   FROM _xlxml.cells
   ;
QUIT;
%LET dataLength = &dataLength;
%LET commentLength = &commentLength;

/*-- Daten lesen --------------------------------------------------------------*/
DATA &out (KEEP=SheetName--Comment);
   LENGTH 
      SheetId     8               /* Nummer des Blatts */
      SheetName   $31             /* Name des Blatts */
      DataType    $12             /* Datentyp (Number, String, DateTime) */
      RowId       8               /* Zeilennummer */
      ColumnId    8               /* Spaltennummer */
      ColumnAlpha $2              /* Spalte im alphanumerischen Format (A .. Z, AA .. IV) */
      data        $&dataLength    /* Zelleninhalt */
      comment     $&commentLength /* Kommentar */
   ;
   FORMAT 
      data    $char&datalength..
      comment $char&commentlength..
   ;
   RETAIN RowId ColumnId;

   SET _xlxml.cells;

   SheetId = SheetCount;

   IF SheetCount NE lag(SheetCount) THEN RowId=0;
   IF SheetCount NE lag(SheetCount) OR RowCount NE lag(RowCount) THEN DO;
      IF RowIndex NE . THEN RowId=RowIndex;
      ELSE RowId=RowId+1;
      ColumnId=0;
   END;
   IF ColumnIndex NE . THEN ColumnId=ColumnIndex;
   ELSE ColumnId=ColumnId+1;

%LET alpha=" ABCDEFGHIJKLMNOPQRSTUVWXYZ";
   ColumnAlpha = left (substr(&alpha, floor((ColumnId-1)/26)+1, 1) !! substr(&alpha, mod(ColumnId-1,26)+2, 1));

RUN;

LIBNAME _xlxml;