Dateioptionen innerhalb von PROC SQL

Aus SAS-Wiki
Wechseln zu: Navigation, Suche

Von Marianne Weires aus: Carina Ortseifen, Grischa Pfister, Heribert Ramroth, Marianne Weires: Tipps und Tricks für den leichteren Umgang mit der SAS Software, KSFE 2010.

Einsatz von Dateioptionen

Im Folgenden wird die grobe Struktur von PROC SQL und der Einsatz von verschiedenen Dateioptionen erläutert. Dateioptionen innerhalb von PROC SQL sind SAS-spezifische Erweiterungen von SQL, sind optional und können nach CREATE TABLE oder FROM folgen.

PROC SQL;
  CREATE TABLE ErgebnisTabelle <(Dateioptionen)> AS
    SELECT ...
    FROM Tabelle <(Dateioptionen)>...;
QUIT;

Tabelle 1: Beispieldaten für die PROC SQL Abfragen

name   A1 A2 A3 A4 A5 A6 A7 A8 A9 A10 B1 B2 B3 B4 B5
Louise  11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
John    21 22 23 24 25 26 27 28 29 30 31 32 33 34 35
Alice   31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

DROP und KEEP

In SQL werden mit dem Asterisk „*” als Abkürzung sämtliche Spalten/Variablen einer Tabelle (oder auch mehrerer Tabellen) ausgegeben. Es können aber auch nur ein paar Variablen ausgewählt werden. Im Gegensatz zum SAS-Datenschritt werden dann die einzelnen Spalten mit Kommata aufgezählt und dann auch in dieser Reihenfolge ausgegeben. Durch die Verwendung der Dateioptionen KEEP und DROP kann gezielt spezifiziert werden, welche Variablen verarbeitet werden sollen und welche nicht. Zusätzlich kann mit weiteren Kürzeln aus dem Datenschritt gearbeitet werden, wie zum Beispiel „-“ oder „--“ zur Spezifikation von Variablenlisten.

Beispiel 1: Einzelne Variablen nicht selektieren
Werden alle Variablen bis auf wenige benötigt (hier: Variable name nicht benötigt), so kann statt die gewünschten Variablen alle einzeln aufzulisten mit „*“ und der Dateioption DROP gearbeitet werden.

PROC SQL;
  SELECT *
    FROM tab(DROP=name);
QUIT;

Beispiel 2: Mehrere Variablen selektieren
Möchte man eine Liste an Variablen auswählen und haben diese einen gemeinsamen Präfix und fortlaufenden numerischen Suffix (z.B. A1, A2 etc.), so kann mit KEEP und „-“ eine Liste spezifiziert werden. Ohne die Dateioption KEEP müssten alle Variablen einzeln aufgelistet werden.

PROC SQL;
  SELECT *
    FROM tab(KEEP=A1-A10);
QUIT;

Mit „--“ werden nur Variablen ausgewählt, die zwischen A6 und B4 im Datensatz tab liegen.

PROC SQL;
  SELECT *
    FROM tab(KEEP= A6--B4);
QUIT;

Mit „:“ können Variabeln mit einem bestimmten Präfix angegeben werden.

PROC SQL;
SELECT *
  FROM tab(KEEP= A:);
QUIT;

Beispiel 3: Beobachtungen selektieren
Sollen basierend auf einer Variablen A1 Beobachtungen ausgewählt werden, die Variable aber selbst nicht mit in der neuen Tabelle kommen, so kann DROP erst innerhalb von CREATE TABLE spezifiziert werden, denn sonst ist die Spalte nicht für mehr die WHERE Anweisung vorhanden.

PROC SQL;
  CREATE TABLE tab2(DROP=A1) AS
    SELECT *
    FROM tab WHERE A1=11;
/*funktioniert nicht*/
CREATE TABLE tab2 AS
  SELECT *
    FROM tab(DROP=A1)
    WHERE A1=11;
QUIT;

Hinweise:

  • Das Selektieren von Variablenlisten funktioniert nur als Dateioption: SELECT A1-A5 FROM... würde eine neue Variable erzeugen mit der Differenz der beiden Variablen A1 und A5.
  • WHERE wird nach FROM aber vor CREATE TABLE ausgewertet.

RENAME

Beispiel 4: Variablen umbenennen
Im Fall von Variablen mit gemeinsamem Präfix und numerischem Suffix lassen sich mit der RENAME Dateioption ganze Variablenlisten einfach umbenennen.

PROC SQL;
  SELECT *
    FROM tab (RENAME=(A1-A5 = Neu_A1-Neu_A5) KEEP=A1-A5);

/*statt*/
  SELECT A1 AS Neu_A1,A2 AS Neu_A2, ... ,A5 AS Neu_A5
    FROM tab;
QUIT;

Hinweis:
KEEP wird vor RENAME ausgeführt.

SORTEDBY

Beispiel 5: Unnötiges Sortieren vermeiden
Mit dieser Dateioption kann angegeben werden, dass ein Datensatz bereits nach der angegebenen Variable sortiert ist und somit zum Beispiel unnötiges Sortieren vermieden werden. Manchmal sind Datensätze bereits sortiert, wenn diese zum Beispiel sortiert vorlagen, als sie eingelesen wurden, ohne dass diese Information in den Metadaten der Tabelle vermerkt ist. Wird nun ein JOIN durchgeführt, so kann SAS einen SORT MERGE JOIN als Verknüpfungsalgorithmus durchführen (dann nämlich wenn die Datensätze zu groß sind für einen HASH JOIN oder es liegt kein passender Index vor für einen INDEX JOIN). Bei SORT MERGE JOIN werden zunächst die beteiligten Tabellen nach der Verknüpfungsvariable sortiert. Liegen eine oder mehrere Tabellen bereits sortiert vor, so kann dies mit SORTEDBY angegeben werden und damit unnötiges Sortieren vermieden werden. Mit der undokumentierten Anweisungsoption _METHOD wird der gewählte Algorithmus angezeigt und auch welche Datensätze sortiert wurden.

PROC SQL _METHOD;
  SELECT tab.A1,tab2.B1
    FROM tab (SORTEDBY=A1) INNER JOIN tab2
    ON tab.A1 = tab2.B1;
QUIT;

Im Log-Fenster wird folgender Hinweis angezeigt:

NOTE: SQL execution methods chosen are:
     sqxslct
          sqxjm
               sqxsort
                    sqxsrc( WORK.TAB2 )
               sqxsrc( WORK.TAB )

sqxjm gibt an, dass ein SORT MERGE JOIN durchgeführt wurde und es wurde nur die Tabelle tab2 sortiert (sqxsort sqxsrc( WORK.TAB2 )).

Hinweis:
Mit PROC CONTENTS kann man sich anzeigen lassen, ob eine Tabelle sortiert ist und nach welcher Variablen.

WHERE

Beispiel 6: Statt Inline View Dateioption WHERE
Statt einer Inline View kann auch WHERE als Dateioption benutzt werden.

PROC SQL;
  SELECT tab.A1
    FROM tab INNER JOIN (SELECT * FROM tab WHERE A1=11) AS tab2
    ON tab.A1 = tab2.B1;
QUIT;

PROC SQL;
  SELECT tab.A1
    FROM tab INNER JOIN tab(WHERE=(A1=11)) AS tab2
    ON tab.A1 = tab2.B1;
QUIT;

Hinweise:

  • Weitere Optionen sind zum Beispiel FIRSTOBS,OBS, IDXWHERE und IDXNAME.
  • Nicht alle Dateioptionen können mit SQL generierten Views innerhalb von PROC SQL (im FROM oder CREATE VIEW Teil) verwendet werden.

Literatur