PROC SQL - Auffinden von unterschiedlichen Tabelleneinträgen

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 2009

Ziel dieses Tipps ist es die Verwendung des Schlüsselwortes DISTINCT zur Auffindung unterschiedlicher Einträge im Rahmen von PROC SQL zu veranschaulichen. Zur weiteren Verarbeitung werden die unterschiedlichen Werte als Array von Makrovariablen oder als Makrovariablen-Array gespeichert. Zusätzlich werden die nicht dokumentierten Anweisungsoptionen METHOD und TREE in Bezug auf DISTINCT kurz vorgestellt.

SQL steht für Structured Query Language und ist eine weit verbreitete Datenbanksprache zur Definition, Abfrage und Bearbeitung von Daten in relationalen Datenbanken. PROC SQL ist die SAS Implementierung von SQL und stellt oft eine Alternative zum SAS Datenschritt dar.

Ausschnitt aus der Syntax der SELECT Anweisung mit relevanten Elementen für die Verwendung von DISTINCT und zur Erstellung von Makrovariablen:

PROC SQL <_METHOD> <_TREE> <NOPRINT>;
  SELECT DISTINCT *|var1 <,...varN>  
    INTO :makrovar1 <,...:makrovarN> <SEPARATED BY zeichen>  
    <NOTRIM>
    FROM bibliothek.tabelle
    <WHERE bedingung> ... ;  

  SELECT ...
QUIT;

Anwendung von DISTINCT

DISTINCT var1 <,...varN) listet alle unterschiedlichen Werte einer Variablen var1 oder einer Kombination von N Variablen auf. Jeder Wert der Variable wird nur einmal berücksichtigt, d.h. Duplikate werden herausgefiltert. DISTINCT * listet alle unterschiedlichen Einträge in einer Tabelle. „*“ steht für alle Variablen/Spalten der Tabelle. Mit DISTINCT werden die Werte bereits aufsteigend sortiert zurückgegeben, da DISTINCT durch ein PROC SORT mit der Option NODUP/NODUPKEY realisiert wird. Dabei werden die Daten sortiert und Duplikate entfernt. Die Verarbeitung von SQL Abfragen kann mit den nicht dokumentierten Anweisungsoptionen METHOD und TREE nachvollzogen werden. Für eine SQL Anweisung zeigt METHOD den dazugehörigen Programmablauf und TREE den detaillierten Programmablauf als Baum im Log Fenster an. Der Output von METHOD und TREE kann sehr hilfreich sein, um den Ablauf von SQL Anweisungen besser zu verstehen und auch den eigenen Code zu optimieren.

Beispieldatensatz

Ein einfacher Datensatz soll die Beispiele veranschaulichen.

PROC SQL;
  CREATE TABLE patient /* leere tabelle erstellen */
  (
     id NUM,
     vorname CHAR(20),
     nachname CHAR(20)
  );
QUIT;
PROC SQL;
  INSERT INTO patient
  VALUES (100, 'Harald', 'Schmidt')
  VALUES (205, 'Martin', 'Haller')
  VALUES (378, 'Diana', 'Jung')
  VALUES (425, 'Eva', 'Hein')
  VALUES (545, 'Florian', 'Merk')
  VALUES (689, 'Martin', 'Kerber')
  VALUES (700, 'Barbara', 'Allers')
  VALUES (889, 'Marion', 'Gran');
QUIT;

Tabelle 1: Beispieldatensatz

id vorname nachname
100 Harald Schmidt
205 Martin Haller
378 Diana Jung
425 Eva Hein
545 Florian Merk
689 Martin Kerber
700 Barbara Allers
889 Marion Gran

Beispiele Beispiel 1 – Einfaches Beispiel mit DISTINCT

PROC SQL; /*1*/
  SELECT DISTINCT vorname 
    FROM patient;
QUIT;
vorname
Barbara
Diana
Eva
Eva
Florian
Harald
Marion
Martin


/*1*/: Es werden nur unterschiedliche Werte der Variablen vorname (aufsteigend) zurückgeliefert. Der Name Martin wird nur einmal aufgelistet.

PROC SQL; /*2*/
  SELECT DISTINCT id,vorname 
    FROM patient;
QUIT;
id vorname
100 Harald
205 Martin
378 Diana
425 Eva
545 Florian
689 Martin
700 Barbara
889 Marion

/*2*/: Es werden nur unterschiedliche Werte der Variablenkombination id,vorname , aufsteigend nach id und vorname, zurückgeliefert. Der Name Martin wird nun zweimal aufgelistet, da er jeweils eine andere id besitzt.

Beispiel 2A – Erstellung von Makrovariablen In diesem Beispiel soll der Output von DISTINCT weiterverarbeitet werden, indem ein Array von Makrovariablen, d.h. eine Liste von Makrovariablen, erzeugt wird.

PROC SQL NOPRINT; /*1*/ 
	SELECT DISTINCT nachname INTO :value1-:value&sysmaxlong 
	FROM patient;
QUIT;

%LET value_num = &sqlobs; /*2*/
%PUT *&sqlobs* *&value1* *&value2* *&value3*...*&&value&sqlobs*;

Output im Log Fenster:

  • 8* *Schmidt* *Haller* *Jung*...*Gran*

/*1*/: Jeder unterschiedliche Wert der Variable nachname wird in einer eigenen Makrovariablen gespeichert, d.h. es entsteht ein Array von Makrovariablen (Abbildung 1). &sysmaxlong gibt den maximalen Integerwert wieder (32-bit Windows: 2.147.483.647). Es wird trotzdem nur die benötigte Anzahl an Makrovariablen erstellt.

/*2*/: &sqlobs gibt die Anzahl der Zeilen, die durch die letzte SQL Anweisung verarbeitet wurden, wieder. Das entspricht bei der SELECT Anweisung der Anzahl der ausgegebenen Zeilen und hier auch der Anzahl der erstellten Makrovariablen.

Tabelle-saswiki.JPG

Abbildung 1: Beispiel 2A – Array von Makrovariablen

Hinweise: Durch die Verwendung von &sysmaxlong ist es nicht notwendig ein SELECT COUNT() als erste Abfrage auszuführen, um die benötigte Anzahl an Makrovariablen zu ermitteln. Mit &sqlobs erhält man die Anzahl an erstellten Makrovariablen. Die Werte der Variable nachname werden ohne überschüssige Leerzeichen in die einzelnen Makrovariablen eingetragen. Falls dies nicht erwünscht ist, dan kann NOTRIM am Ende der INTO Anweisung gesetzt werden.

Beispiel 2B – Erstellung von Makrovariablen In diesem Beispiel wird ein Makrovariablen-Array erzeugt, d.h. eine einzige Makrovariable, die die gesamte Liste von Werten enthält.

PROC SQL NOPRINT; /*1*/
  SELECT DISTINCT nachname INTO :var_values SEPARATED BY ','
    FROM patient;
QUIT;
%PUT *&var_values*; 

Output im Log Fenster:

  • Schmidt,Haller,Jung,Hein,Merk,Kerber,Allers,Gran*

/*1*/: Jeder unterschiedliche Wert der Variable nachname wird in eine einzige Makrovariable varvalues hintereinander und getrennt durch ein „ , “ eingetragen, d.h. es entsteht ein Makrovariablen-Array (Abbildung 2).

Tabelle-saswiki 2.JPG

Abbildung 2: Beispiel 2B – Makrovariablen-Array

Ist die Ausgabe mit Anführungszeichen gewünscht, so ist dies mit der Funktion QUOTE() möglich:

PROC SQL NOPRINT; /*1*/
  SELECT DISTINCT QUOTE(STRIP(nachname)) INTO :var_values SEPARATED   
    BY ','
    FROM patient;
QUIT;
%PUT *&var_values*; 

Output im Log Fenster:

  • "Allers","Gran","Haller","Hein","Jung","Kerber","Merk","Schmidt"*

/*1*/: Anführungszeichen

Hinweis: Überschüssige Leerzeichen müssen vor der Anwendung der QUOTE() Funktion mit der Funktion STRIP() entfernt werden.

Verarbeitung von DISTINCT

Beispiel 3 – Output von _METHOD und _TREE

PROC SQL _METHOD _TREE;
  SELECT DISTINCT nachname 
    FROM patient;
QUIT;

Output im Log Fenster für _METHOD:

NOTE: SQL execution methods chosen are:
      sqxslct *3*
          sqxuniq *2*
          sqxsrc( WORK.PATIENT ) *1*

sqx im Output von METHOD steht für SQL Execution code. Der Output wird von rechts nach links und von unten nach oben gelesen. Die zum Output hinzugefügten Nummern *1* bis *3* geben die Verarbeitungsreihenfolge an.

sqxsrc kennzeichnet die Quelltabelle
sqxuniq weißt darauf hin, dass DISTINCT mit einem PROC SORT mit der Option NODUP/NODUPKEY umgesetzt wird
sqxslct zeigt die Ausführung einer SELECT Anweisung an

Output im Log Fenster für TREE:

400

Der Output von TREE wird ebenfalls von rechts nach links und von unten nach oben gelesen. Von rechts nach links werden Anweisungen/Informationen zusammengefasst und eine Ebene weitergegeben. Die zum Output hinzugefügten Nummern *1* bis *12* geben die Reihenfolge an. Der Output kann folgendermaßen interpretiert werden:

SYM-V selektieren von Variablen aus einer Tabelle. Die Nummer deutet auf die Reihenfolge der Variablen in der Tabelle hin, d.h. ob erste, zweite,

dritte ... Variable in der Tabelle. Flag ist für interne Weiterverarbeitung vorgesehen

ASC enthält die Information, nach welcher Spalte auf der rechten Seite auf-steigend sortiert werden soll
TABL[WORK] deutet auf die entsprechende Tabelle in der Bibliothek WORK hin. Datenschrittoptionen werden in opt= vermerkt
OBJ fasst zusammen, welche Variablen eine Ebene höher gereicht werden
ORDR fasst die Information zusammen, nach welchen Spalten wie sortiert werden soll. Diese Information wird eine Ebene weitergegeben an UNIQ, welches dann erst das Sortieren durchführt
empty Platzhalter für weitere mögliche Anweisungen für den SQL Optimierer
SRC fasst die Datenquelle zu einem Ergebnisobjekt zusammen
UNIQ eliminieren von Duplikaten, wird durch ein PROC SORT mit der Option NODUP/NODUPKEY implementiert
SSEL weißt auf eine SQL Abfrage vom Typ SELECT hin

Hinweis: Der hier beschriebene Output der Optionen METHOD und TREE bezieht sich auf SAS 9.1 und kann von anderen SAS Versionen abweichen. METHOD und TREE bieten eine exzellente Möglichkeit die Verarbeitung von SQL Anweisungen nachzuvollziehen. So können sie z.B. genutzt werden, um sich den gewählten Algorithmus zur Umsetzung eines SQL Joins anzeigen zu lassen (zum Beispiel Hash Join, Index Join, Sort Merge Join oder Step Loop Join).

Kurzes Fazit PROC SQL und DISTINCT bieten eine flexible und kompakte Vorgehensweise, um Wertelisten als Arrays von Makrovariablen und auch als Makrovariablen-Arrays zu erstellen. Anwendungsszenarien sind zum Beispiel das Auflisten aller unterschiedlichen Werte in einer WHERE IN (...) Anweisung, sowie in WHERE ANY (...) oder WHERE ALL (...) Anweisungen in SQL. Obwohl die Anweisungsoptionen _METHOD und _TREE nicht dokumentiert sind, bieten sie trotzdem die Möglichkeit etwas hinter die Kulissen von PROC SQL zu schauen und die Schritte, die während der Ausführung einer SQL Anweisung ablaufen, genauer zu verstehen. Dieses Verständnis ist hilfreich, um die Performanz der eigenen SQL Anweisungen verbessern zu können.

Literatur

  • SAS Institute Inc., 2004. SAS® 9.1 SQL Procedure User’s Guide. Cary, NC: SAS Institute Inc.
  • Long, Stuart and Heaton, Ed (2008), Using the SAS DATA Step and PROC SQL to Create Macro Arrays Proceedings of SAS Global Forum 2008 San Antonio, Texas, USA.
  • Lavery, Russ (2005), The SQL Optimizer Project: _Method and _Tree in SAS®9.1, Proceedings of the Thirtieth Annual SAS® Users Group International Conference Philadelphia, Pennsylvania, USA.