ODS Tagset ExcelXP

Aus SAS-Wiki
Wechseln zu: Navigation, Suche

Von Grischa Pfister aus: Hans-Peter Altenburg, Carina Ortseifen, Tanja Petrowitsch, Grischa Pfister, Wilfried Schollenberger: Tipps & Tricks für den leichteren Umgang mit der SAS Software, KSFE 2007

Mit dem Tagset ExcelXP stellt SAS eine weitere Schnittstelle in die Excel-Welt bereit, diesmal schreibt SAS direkt das von Microsoft vorgegebene Excel-spezifische XML-Format. Dabei bietet der Tagset folgende Besonderheiten:

  • Benennung der Tabellenblätter
  • Je SAS-Tabelle ein Blatt in Excel bzw. je By-Gruppe, Prozedur,…
  • Überschriften (Spalten und Zeilen) fixieren
  • Automatische Spaltenfilter
  • Formatierung mit Excel-Formaten
  • Verwendung von Excel-Formeln
  • Und viele mehr…

Der Aufruf des Tagsets folgt den normalen Konventionen des ODS, um den eigentlichen Code wird eine Klammer aus zwei ODS Anweisungen gesetzt, die erste öffnet den ODS Kanal, die zweite schließt ihn wieder.

Ods Tagsets.ExcelXP file=„path" options(optionen);
…
Ods Tagsets.ExcelXP Close;

In den Tagset ist die vollständige Dokumentation gleich mit eingearbeitet, durch den Aufruf des folgenden Programms wird sie in das LOG ausgegeben.

Ods Tagsets.ExcelXP file="ExcelXP001.xls" options(doc="help");
Ods Tagsets.ExcelXP close;

Der Tagset wird über Optionen gesteuert, die per ODS Anweisung gesetzt werden, zum einen beim Öffnen des Kanals, aber auch vor einer individuellen Tabelle:

Ods Tagsets.ExcelXP file=„path“ options(optionen);
…
Ods Tagsets.ExcelXP options(optionen);
…
Ods Tagsets.ExcelXP Close;

Einfache Optionen sind z.B. die Seitenausrichtung (portrait=Hochformat oder landscape=Querformat) und der Zoom-Faktor für die Ansicht.

Ods Tagsets.ExcelXP file="&root/excelXP002.xls"
  options(orientation="portrait" zoom="100")
;
Proc Print data = Sashelp.Prdsale(obs=50);
Run;
Ods Tagsets.ExcelXP options(orientation="landscape" zoom="200");
Proc Print data = Sashelp.class;
Run;
Ods Tagsets.ExcelXP Close;

Im Beispiel werden zwei Tabellen ausgegeben, die erste mit dem Zoom-Faktor 100 in Hochformat, die zweite mit dem Zoom-Faktor 200 in Querformat.

Die Optionen Frozen_Headers und Frozen_RowHeaders sorgen dafür, dass Spalten- und Zeilenüberschriften fixiert werden, d.h. wenn der Benutzer im Excel-Blatt scrollt, werden die Überschriften weiter angezeigt. Das macht für größere Ausgaben von Proc Print oder Proc Tabulate Sinn. Excel unterstützt die Vorgabe von automatischen Spaltenfiltern, dabei wird zu der Spaltenüberschrift ein Kontextmenü hinzugefügt, welches das Sortieren und Filtern der Spaltenwerte erlaubt. Mit Hilfe der Option Autofilter kann dies aus SAS heraus gesteuert werden, allerdings nur für eine oder mehrere aufeinander folgende Spalten.

Ods Tagsets.ExcelXP file="&root/excelXP003.xls"
  options(frozen_headers="yes" frozen_rowHeaders="yes")
;
 
Proc Tabulate data = Sashelp.Prdsale;
  Class country region product;
  Var actual;
  Table country*Region, product*actual*sum;
Run;
 
Ods Tagsets.ExcelXP 
  options(frozen_headers="no" frozen_rowHeaders="no"
          autoFilter="1-3")
;
Proc Print data = Sashelp.class noobs ;
  Var sex age name height weight;
Run;
 
Ods Tagsets.ExcelXP Close;

Das Verwenden von Excel-Formaten gestaltet sich etwas komplexer, da es nicht über Optionen des Tagsets geschaltet werden kann, sondern die Format-Information spaltenbezogen mit Hilfe eines Style-Attributes an den Tagset übergeben wird. Die Style-Anweisung hat die Form Tagattr=“Excel-Format“. Dabei kann das Excel-Format eine Anweisung sein, wie eine Ziffer darzustellen ist (0 als Platzhalter für signifikante Zahlen, # als Platzhalter für nichtsignifikante Zahlen), genauso gut können aber auch Farbkodierungen angegeben werden (in diesem Falle Format in Excel erstellen und dann im XML-Format speichern – daraus lässt sich ablesen, wie das Format in SAS angegeben werden muss. Hier ein paar Beispiele:

  • tagattr=“format:000.00“
  • tagattr=“###.###.###.0#“
  • tagattr=“Red][<=100];[Blue][>100]“

Die Angabe von “format:“ vor dem eigentlichen Excel-Format ist nicht zwingend. Das letzte Beispiel enthält eine bedingte Formatierung, Werte kleiner/gleich 100 werden in roter Farbe dargestellt, solche größer 100 blau. Ein vollständiges Programm sieht dann so aus:

Ods Tagsets.ExcelXP file="&root.\ExcelXP004.xls";

Proc Print data = Sashelp.Class Noobs;
  Var name sex age;
  Var weight height / style(data)={tagattr="0000.00"};
Run;

Proc Print data = Sashelp.Class Noobs;
  Var name sex age;
  Var weight height/ 
    Style(data)={tagattr='format:[Red][<=100];[Blue][>100]'};
Run;

Ods Tagsets.ExcelXP Close; 

Im ersten Falle erfolgt die Darstellung der Werte für Weight und Height mit führenden Nullen und zwei Nachkommastellen, bei dem zweiten Beispiel werden die Werte farblich unterschiedlich hervorgehoben.

Es gibt zwei Varianten, Excel-Formeln aus SAS heraus zu steuern. Zum einen gibt es die Möglichkeit, alphanumerische Spalten zu erzeugen, die Excel-Formeln enthalten. Dabei kann mit relativen Bezügen auf die Spalten und Zeilen des Excel-Arbeitsblattes verwiesen werden, die Syntax ist R[+/-n]C[+/-n]. Die Koordinate RC[-1] bezieht sich auf den Wert in der ersten Spalte nach links, RC[-2] auf den Wert in der zweiten Spalte nach links, beides in der gleichen Zeile. R[-1]C[-1] bezieht sich auf den Wert der eine Zeile weiter oben in der direkt links gelegenen Spalte enthalten ist. Beispiel:

differenz = "=RC[-2] - RC[-1]";

Außerdem können Excel-Funktionen verwendet werden wie z.B. Abs(), Sum() etc., allerdings dürfen nur die englischsprachigen Funktions-Namen verwendet werden. Im Internet finden sich Übersetzungshilfen (z.B. unter http://www.wi.euv-frankfurt-o.de/lehre/wi/materialien/Deutsch-Englisch-Funktionen.xls), ein Speichern des Arbeitsblattes in XML-Format liefert ebenfalls die Übersetzung.

Summe = "=sum(RC[-2] : RC[-1])";

Das funktioniert aber nur mit denjenigen Funktionen, die keine XML-spezifischen Sonderzeichen wie doppelte Anführungszeichen, >, <, o.ä. enthalten. Wenn Formeln verwendet werden sollen, die solche Funktionen enthalten, muss die zweite Variante verwendet werden, die wieder das Style-Attribut Tagattr benutzt. Hier kann nämlich neben dem Format auch eine Formel hinterlegt werden. Aus der Excel-Formel

=WENN(E2 > 100; "gut";"schlecht")

wird im SAS-Code

tagattr='formula:=IF(RC[-1] > 100,
         "gut","schlecht")'};

Auch hier bietet es sich an, zunächst in einem Excel-Arbeitsblatt die Formel zu erstellen und dann das ganze in XML-Format zu speichern. Die resultierende XML-formatierte Formel kann dann in das Style-Angabe übernommen werden. Das vollständige Beispiel sieht dann so aus:

Proc Sql;
  Create View Work.Differenz As
    Select country, product, actual, predict, 
      . As Summe, . As Urteil
      From Sashelp.Prdsale(obs=10)
  ;
Quit;

Im ersten Schritt werden zwei leere Spalten hinzugefügt, für die später mit Hilfe des Style-Attributes eine Formel hinterlegt wird.

Ods Listing Close;
Ods Tagsets.ExcelXP
  file="&root.\ExcelXP006.xls"
;

Proc Print data = Work.Differenz Noobs;
  Var country product;
  Var actual predict / style = {cellwidth=3 cm};
  Var summe / style = {cellwidth=3 cm 
                           tagattr='formula:sum(RC[-2],RC[-1])'};
  Var urteil / style = {cellwidth=3 cm 
                        tagattr='formula:=IF(RC[-1] > 1200,
                        "gut","schlecht")'};
Run;

Ods Tagsets.ExcelXP Close;

Die Formel für die Spalte Summe definiert eine Summierung der beiden linken Spalten, die Spalte Urteil wertet ihrerseits die Summe aus und zeigt den Text „gut“ an, wenn der Wert größer 1200 ist, ansonsten den Text „schlecht“.

Literatur