PROC SQL

Aus SAS-Wiki
Wechseln zu: Navigation, Suche

PROC SQL ist eine im Modul Base SAS enthaltene SAS-Prozedur. Sie leistet die Einbindung von SQL in SAS. SQL steht für Structured Query Language, eine standardisierte und weit verbreitete Datenbanksprache zur Definition, Abfrage und Bearbeitung von Daten in relationalen Datenbanken wie beispielsweise MySQL, PostgreSQL oder der in Microsoft Access verwendeten Jet Engine.

Eine relationale Datenbank ist eine Datenbank, die aus Tabellen besteht, welche sich aufeinander beziehen, dabei gründet die Beziehung auf einem Schlüsselwert. SQL wurde in den 70er Jahren von IBM auf der Grundlage des Artikels A Relational Model of Data for Large Shared Data Banks (1970) von Edgar F. Codd entworfen. SQL ist seit 1986 und 1987 ein ANSI- bzw. ISO-Standard, der bereits mehrfach spezifiziert wurde bzw. noch wird. Neben einem bestimmten SQL-Standard unterstützen Datenbanksysteme meist Teile höherer Standards sowie eigene SQL-Erweiterungen. PROC SQL ist die SAS-Implementierung von SQL und stellt oft eine Alternative zum Datenschritt und damit kombinierten SAS-Prozeduren wie PROC SUMMARY dar. Seit der SAS-Version 6 ist die Prozedur Teil von Base SAS. SAS unterstützt den Standard SQL-92, ist aber nicht vollkompatibel damit.

Einsatz von PROC SQL

Der Einsatz von PROC SQL ist sehr vielfältig; es können beispielsweise folgende Operationen sehr einfach und übersichtlich umgesetzt werden:

  • Erstellung von Summenstatistiken
  • Erzeugung von Tabellen, Views und Indizes
  • Verknüpfung von Tabellen oder Views
  • Erzeugung von Makrovariablen
  • Zugriff und Abfrage externer Datenbankmanagementsysteme (DBMS)
  • Erstellung von Reports

Procsql einsatz.gif

Hinsichtlich der Anwendung gibt es viele Bereiche, in denen die Lösung eines Problems sowohl mit SQL als auch mit dem Datenschritt beziehungsweise einer anderen Prozedur möglich ist. Da sich die beiden Alternativen jedoch hinsichtlich ihrer prinzipiellen Vorgehensweise unterscheiden, ist eine von beiden häufig effizienter. Die folgende Auflistung vergleicht einige Merkmale von PROC SQL und Datenschritt:


Datenschritt PROC SQL
Sequentielles Vorgehen Relationelles Datenbankmodell
Geordnete Beobachtungen Keine Ordnung
Notwendiges Sortieren Sortieren nicht erforderlich
Totale Kontrolle über Verantwortung für die Datenverarbeitung Optimale Verarbeitungsmethoden werden automatisch ausgewählt


Die drei anschließenden Beispiele zeigen alternative Umsetzungen der gleichen Operation im Datenschritt und in PROC SQL.


Beispiel 1: Ausgabe der voneinander verschiedenen Beobachtungen in einer Datei:

Datenschritt

   PROC SORT DATA=sashelp.class out=klasse;
    BY sex age;
   RUN;
   DATA neu;
    SET klasse;
    BY sex age;
    IF FIRST.age;
   RUN;
   PROC PRINT DATA=neu;
    VAR sex age;
   RUN;

PROC SQL

   PROC SQL;
     SELECT DISTINCT sex, age
     FROM sashelp.class;
   QUIT;


Beispiel 2: Berechnen neuer Variablen (hier des Body Mass Index anhand von Gewicht in lbs und Größe in inches) und abspeichern in einer neuen Tabelle

Datenschritt

   DATA bmi;
     SET sashelp.class;
     bmi=weight*0.45359237/(height*2.54/100)**2;
   RUN;

PROC SQL

   PROC SQL;
     CREATE TABLE bmi AS
     SELECT * 
     , weight*0.45359237/(height*2.54/100)**2 AS bmi
     FROM sashelp.class;
   QUIT;


Beispiel 3: Bildung eines kartesischen Produkts:

Datenschritt

   DATA kart_prod;
     SET sashelp.class(KEEP=weight)  NOBS=letzte;
     DO i=1 TO letzte ;
       SET sashelp.class(KEEP=height )POINT=i;
       OUTPUT;
     END;
   RUN;

PROC SQL

   PROC SQL;
     CREATE TABLE kart_prod AS
     SELECT a.weight, b.height
     FROM sashelp.class AS a, sashelp.class AS b;
   QUIT;

Extraktion von Daten aus Tabellen oder Views

Zum Hauptartikel:Abfragen unter PROC SQL

Mit der SQL-eigenen SELECT-FROM-WHERE-Struktur lassen sich Datensätze und Variablen aus einer oder mehreren SAS-Tabellen oder Sichten zusammenführen, neue Spalten berechnen und statistische Kennzahlen ausgeben. Kennzahlen können zudem mit dem GROUP BY statement nach beliebig vielen Variablen gruppiert werden. Das ORDER BY stellt die Funktion zur Verfügung, die Ergebnistabelle nach beliebig vielen Variablen, die im SELECT statement aufgelistet sind, auf- oder absteigend zu sortieren.

Erzeugen und Löschen von SAS-Tabellen

Zum Hauptartikel:Erstellen von SAS-Tabellen unter PROC SQL

Zum Hauptartikel:Löschen von SAS-Tabellen unter PROC SQL

Mit dem CREATE-statement werden SAS-Tabellen neu angelegt, das DROP-statement löscht bestehende DATASETS.

Modifizieren von SAS-Tabellen

Hinzufügen, verändern oder löschen von Variablen

Mit ALTER TABLE...ADD/DROP/MODIFY können neue Spalten zu einer bereits bestehenden Tabelle hinzufügt, Spalten gelöscht oder verändert werden.

   PROC SQL; 
      ALTER TABLE sql.projekt_kopie 
      ADD auftraggeber CHAR(20),dringlichkeit NUM   
      DROP budget 
      MODIFY proj_name CHAR(50); 
   QUIT;
   
   NOTE: Table SQL.PROJEKT_KOPIE has been modified, with 6 columns.


Hinzufügen, verändern oder löschen von Beobachtungen

Mit dem INSERT INTO Befehl gibt es drei Möglichkeiten, neue Beobachtungen in eine Tabelle einzufügen. Die neuen Einträge werden dabei jeweils ans Ende der Tabelle angehangen.

  • INSERT INTO...SET: Einfügen mit der SET Anweisung
  • INSERT INTO...VALUES: Einfügen mit der VALUES Anweisung
  • INSERT INTO...SELECT: Einfügen aus einer Abfrage
   PROC SQL; 
     INSERT INTO sql.projekt_kopie 
       SET proj_id = 107, 
              proj_name = 'Neues Projekt 1', 
              start_dat = '12APR2000'd, 
              ende_dat = '12JAN2001'd,  
              budget = 10000;
     INSERT INTO sql.projekt_kopie(proj_id,proj_name,start_dat, ende_dat,budget) 
       VALUES(109,'Neues Projekt 3','15MAR2007'd,'26MAR2007'd,45000) 
       VALUES(110,'Neues Projekt 4','18JUN2008'd,.,30000);  /*ende_dat ist leer*/
     INSERT INTO sql.projekt_kopie 
       SELECT * 
          FROM sql.projekt; 
   QUIT;
  • UPDATE

Mit diesem Befehl können alle oder ausgewählte Beobachtungen in einer Tabelle geändert werden.

   PROC SQL; 
      UPDATE sql.projekt_kopie 
         SET budget = budget * 10; /* alle Einträge */ 
      UPDATE sql.projekt_kopie 
         SET budget = budget / 10 
         WHERE proj_id = 101; 
    QUIT;
  • DELETE

Mit diesem Befehl können ausgewählte oder alle Beobachtungen in einer Tabelle gelöscht werden.

   PROC SQL; 
     DELETE 
         FROM sql.projekt_kopie 
         WHERE proj_id = 101; 
     DELETE 
        FROM sql.projekt_kopie; /* alle Einträge */ 
   QUIT;

Verknüpfen von Tabellen und Views

Mit den JOIN-Befehlen können Tabellen ähnlich dem MERGE-Befehl miteinander verknüpft werden und somit Beziehungen zwischen Tabellen aufgezeigt werden. Die Tabellen werden horizontal miteinander verknüpft (im Gegensatz zu den Set-Operatoren, die die Daten vertikal verknüpfen). Die Syntax eines Joins (von zwei Tabellen) ist im Folgenden dargestellt:

  PROC SQL; 
    SELECT ...  
       FROM Tabelle1|View1 <Join Typ> Tabelle2|View2  <ON|WHERE (Join Bedingung)> ...
  QUIT;

Der Join-Typ gibt die Art der Verknüpfung an, beispielsweise CROSS, INNER oder OUTER JOIN. Die Join-Bedingung definiert die Kriterien für den Join, das heißt wie und über welche Variablen die Tabellen verknüpft werden sollen. Je nach Join-Kriterium kann jeder Join Typ noch zusätzlich als EQUI- und NON-EQUIJOIN kategorisiert werden.

EQUIJOIN und NON-EQUIJOIN

Ein Equijoin basiert auf einem „=” in der Join-Bedingung. Non-Equijoins basieren, wie die Bezeichnung schon andeutet, auf einem „nicht =” in der Join-Bedingung oder auf der Verbindung von mehreren Join Bedingungen mit einem OR.

CROSS JOIN

Wird kein Join-Kriterium spezifiziert, wird ein CROSS JOIN ausgeführt. Dabei werden alle Beobachtungen der ersten Tabelle mit allen Beobachtungen der zweiten Tabelle verknüpft. Diese vollständige Kombination wird auch Kartesisches Produkt genannt. Die Ergebnistabelle enthält so viele Zeilen, wie das Produkt der Zeilenzahlen der Ausgangstabellen angibt. Deshalb ist dieser Join besonders rechenintensiv und sollte in der Praxis, wenn möglich, vermieden werden.

  PROC SQL; 
    SELECT * FROM sql.gewicht AS a, sql.groesse AS b; 
  /*oder 
    SELECT * FROM sql.gewicht AS a CROSS JOIN sql.groesse AS b;*/ 
  QUIT;
  NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized. 

Meistens will man nur eine Teilmenge des kartesischen Produktes. Je nachdem welche Teilmenge man möchte, unterscheidet man zwischen INNER und OUTER JOIN.

INNER JOIN

Ein Inner Join gibt nur die Datenreihen aus den Tabellen zurück, welche eine gemeinsame Übereinstimmung nach dem Join Kriterium besitzen. Das Ergebnis eines Inner-Equi-Joins lässt sich hinsichtlich der spezifizierten Variable als eine Art „Schnittmenge“ der verknüpften Tabellen interpretieren.

  PROC SQL; 
    SELECT a.*,b.groesse 
      FROM sql.gewicht AS a INNER JOIN sql.groesse AS b ON a.name = b.name; 
  /* oder */
    SELECT a.*,b.groesse 
       FROM sql.gewicht AS a, sql.groesse AS b WHERE a.name = b.name; 
  QUIT;

OUTER JOIN

Outer Joins geben als Ergebnis auch Datenreihen zurück, die keine übereinstimmenden Daten in der anderen Tabelle besitzen. Als Ergebnis wird eine Tabelle ausgegeben, die erstens alle gemeinsamen Beobachtungen enthält und zweitens zusätzlich die restlichen Zeilen der linken (LEFT JOIN) Tabelle, der rechten Tabelle (RIGHT JOIN) bzw. die restlichen Zeilen von beiden Tabellen (FULL JOIN) enhält. Mit COALESCE können Spalten in der Ausgabe überlagert werden.

LEFT JOIN

Beim Left Join bzw. Left Outer Join ist die linke Tabelle die Mastertabelle, das heißt alle Beobachtungen der linken Tabelle werden in Kombination mit den entsprechenden Beobachtungen der rechten Tabelle ausgegeben.

  PROC SQL; 
    SELECT a.name as nameA,a.gewicht, b.name as nameB,b.groesse 
      FROM sql.gewicht AS a LEFT JOIN sql.groesse AS b ON a.name = b.name; 
  QUIT;
  PROC SQL; 
    SELECT COALESCE(a.name,b.name) AS name,a.gewicht,b.groesse 
       FROM sql.gewicht AS a LEFT JOIN sql.groesse AS b ON a.name = b.name; 
  QUIT; 
RIGHT JOIN

Beim Right Join bzw. Right Outer Join ist die rechte Tabelle ist die Mastertabelle, das heißt alle Beobachtungen der rechten Tabelle werden in Kombination mit den entsprechenden Beobachtungen der linken Tabelle ausgegeben.

FULL JOIN

Der Full Join bzw. Full Outer Join ist eine Kombination von Left und Right Join. Es werden zusätzlich zu den Datensätzen aus zwei Tabellen, bei denen das Join-Kriterium erfüllt ist, auch alle Datensätze aus der linken und der rechten Tabelle mit in das Ergebnis einfügt, welche keine Entsprechung in der jeweils anderen Tabelle haben.

  PROC SQL; 
    SELECT a.name as nameA,a.gewicht, b.name as nameB,b.groesse 
      FROM sql.gewicht AS a FULL JOIN sql.groesse AS b ON a.name = b.name; 
  QUIT;

Erstellen von Sichten (Views)

Datenbankabfragen (Pass-through facility)

Allgemeine Syntax

PROC SQL ist eine interaktive Prozedur und bleibt bis zu einem QUIT (oder einem anderen Datenschritt oder Prozedurbeginn) aktiv. Es können mehrere SQL-Anweisungen, getrennt durch einen Strichpunkt, innerhalb eines SQL Blocks spezifiziert werden.

Aufruf der Prozedur

   PROC SQL <OPTIONEN>; 
      CREATE TABLE ...; 
      CREATE VIEW ...; 
      CREATE INDEX ...; 
      
      SELECT ...; 
      DROP TABLE ...; 
      DROP VIEW ...; 
      DROP INDEX ...; 
   QUIT; 

Statements

Ausgabe von Ergebnissen

Sofern eine Ausgabe erfolgt, wird diese entweder im OUTPUT- beziehungsweise im LOG-Fenster angezeigt oder in einer SAS-Tabelle beziehungsweise einer Makrovariablen gespeichert. Ins OUTPUT-Fenster wird das Ergebnis einer Abfrage immer dann geschrieben, wenn ein SELECT-Statement ohne ein CREATE TABLE oder CREATE VIEW statement verwendet wird:

   PROC SQL;
    SELECT age
    FROM sashelp.class
    WHERE age>12
    ;
   QUIT;

Um das gleiche Ergebnis in einer SAS-Tabelle abzuspeichern, muss ein CREATE-TABLE-Statement hinzugefügt werden:

   PROC SQL;
    CREATE TABLE teenager AS
    SELECT age
    FROM sashelp.class
    WHERE age>12
    ;
   QUIT;

Außerdem lassen sich mit der INTO-Ergänzung Werte einer einzelnen Spalte direkt in eine Makrovariable schreiben, wobei einzelne Werte mit einem beliebigen Zeichen getrennt werden können:

   PROC SQL;
    SELECT age INTO :altersliste SEPARATED BY ', '
    FROM sashelp.class
    WHERE age>12
    ;
   QUIT;

speichert alle Werte der Spalte age aus Tabelle sashelp.class, die größer als 12 sind, in die Makrovariable altersliste und fügt zwischen je zwei Werten ein Komma gefolgt von einem Leerzeichen ein.


Wird das DESCRIBE-Statement aufgerufen, wird im LOG-Fenster ein PROC SQL code ausgegeben, mit dem die Tabelle oder View erstellt werden kann und somit Metadaten über sie enthält:

   PROC SQL;
    DESCRIBE TABLE sashelp.class;
   QUIT;


Terminologie

Herkömmliche SAS-Bezeichnungen und Begriffe aus SQL unterscheiden sich hinsichtlich der Terminologie. Die Tabelle enthält die wichtigsten Konzepte tabellenorientierter Datenverarbeitung und ihre jeweilige Benennung:


SAS Datenschritt SQL Datenverarbeitung allgemein
Datei, SAS-Tabelle Tabelle Datei, File
Beobachtung Zeile Record
Variable Spalte Feld


Im LOG-Fenster werden die Begriffe in den Hinweisen (Notes), welche Informationen über die im letzten Schritt erstellte Tabelle geben, entsprechend der angewendeten Methode gewählt.

Weblinks