Einführung in PROC SQL
Werbung
Einführung in PROC SQL 10.07.2008 Einführung in PROC SQL Marianne Weires Überblick Marianne Weires C050 Page 2 • Was ist SQL und PROC SQL? • PROC SQL und SAS DATA Step • Data Definition Language: Erstellen/Löschen von Tabellen/Views/Indizes (CREATE, DROP) • Data Manipulation Language: Struktur von SQL Abfragen (SELECT) Einfügen/Ändern/Löschen (INSERT, UPDATE, DELETE) Verknüpfen von Tabellen (JOIN) Geschachtelte Abfragen (Subqueries) Verknüpfen von Abfragen (Set Operatoren) • Performanz und PROC SQL • SAS und relationale Datenbanken (SAS/ACCESS Software) SAS-Treff am URZ Heidelberg 1 Einführung in PROC SQL Marianne Weires C050 10.07.2008 Was ist SQL und PROC SQL? Page 3 Structured Query Language (SQL): • Standardisiert, weit verbreitet in relationalen Datenbanken (z.B. Oracle, MySQL, Access etc.) • Relationale Datenbank: Datenbank mit Tabellen und Relationen (E.F.Codd) • Fourth-Generation Language • ISO- und ANSI-Standard • Datenbankhersteller mit eigenen SQL-Erweiterungen PROC SQL: • SAS Implementierung von SQL • Teil der SAS-Base Software • SAS unterstützt viele Features des SQL Standards (SQL-92) Marianne Weires C050 PROC SQL und SAS DATA Step Page 4 • Terminologie SQL Term SAS Term Tabelle SAS data file Zeile Beobachtung Spalte Variable Datenbank Bibliothek • Syntax proc sql <option(s)>; /* anfang */ create table ...; /* sql befehle*/ select ...; drop table ...; ... quit; /* ende */ SAS-Treff am URZ Heidelberg 2 Einführung in PROC SQL 10.07.2008 PROC SQL und SAS DATA Step Marianne Weires C050 Page 5 • Kategorien von Befehlen in SQL Data Definition Language (DDL) Definition des Datenbankschemas Data Manipulation Language (DML) Datenmanipulation Data Control Language (DCL) Rechteverwaltung PROC SQL und SAS DATA Step Marianne Weires C050 Page 6 • Kategorien von Befehlen in SQL Data Definition Language (DDL) Definition des Datenbankschemas Data Manipulation Language (DML) Datenmanipulation Data Control Language (DCL) Rechteverwaltung SAS-Treff am URZ Heidelberg 3 Einführung in PROC SQL 10.07.2008 Beispieldatensätze Marianne Weires C050 Page 7 Relation Beispieldatensätze Marianne Weires C050 Page 8 my_db.patients ID SEX PRIMMD BIRTHDTE LASTNAME FIRSTNAME 1 1 1972 10AUG1938 Bauer Jürgen 2 2 1972 17MAR1925 Betz Stefan 3 1 1989 02JUL1918 Casper Martha 4 1 4003 25MAY1916 Hemmingw Greg 5 2 1972 31AUG1937 Humboldt Karla ... ... ... ... ... ... my_db.admits PT_ID ADMDATE DISDATE MD_ID HOSP_ID DEST BP_SYS BP_DIA PRIMDX 1 07FEB1999 08FEB1999 3274 1 1 188 85 410.0 1 12APR1999 25APR1999 1972 1 1 230 101 428.2 1 10SEP1999 19SEP1999 3274 2 2 170 78 813.9 1 06JUN1998 12JUN1998 3274 2 9 185 94 428.4 3 15MAR1998 15MAR1998 2322 3 9 74 40 ... ... ... SAS-Treff am URZ Heidelberg ... ... ... ... ... 431 ... 4 Einführung in PROC SQL 10.07.2008 Beispieldatensätze Marianne Weires C050 Page 9 my_db.hospital HOSP_ID HOSPNAME TOWN 1 Big University Hospital NewMitfo 841 1 2 Our Lady of Charity NorthMit 645 2 3 Veteran’s Administration WestMitf 1176 3 4 Community Hospital Derbyvil 448 1 5 City Hospital NewMitfo ... ... ... NBEDS TYPE 1025 ... 1 ... my_db.doctors MD_ID LASTNAME HOSPADM 1972 Fitzhugh 1 1972 Fitzhugh 2 2322 Mac Arthur 1 2322 Mac Arthur 3 2998 Rosenberg ... ... 4 ... Data Definition Language Marianne Weires C050 Page 10 • Erstellen von Tabellen (CREATE) 1) aus Spaltenbeschreibungen proc sql; create table my_db.doctors /* leere tabelle erstellt */ ( MD_ID num, LASTNAME char(8), HOSPADM num ); quit; * proc sql; describe table my_db.doctors; quit; SAS-Treff am URZ Heidelberg 5 Einführung in PROC SQL 10.07.2008 Data Definition Language Marianne Weires C050 Page 11 2) anlog einer existierenden Tabelle proc sql; /* leere tabelle erstellt */ create table my_db.doctors_kopie like my_db.doctors; quit; 3) aus einer Abfrage * proc sql; create table my_db.doctors_kopie as /* tabelle mit inhalt */ select * from my_db.doctors; quit; Data Definition Language Marianne Weires C050 Page 12 • Erstellen von Views (CREATE) - eine View ist eine gespeicherte Abfrage - speichersparend - virtuelle Tabelle, die u.U. mehrere Tabellen zusammenführt - „versteckt“ komplizierte Abfragen, oder auch Zeilen proc sql; create view my_db.diagnosis_view as select * from my_db.admits where primdx like '410%'; /* primary diagnosis */ select * from my_db.diagnosis_view; quit; SAS-Treff am URZ Heidelberg 6 Einführung in PROC SQL 10.07.2008 Data Definition Language Marianne Weires C050 Page 13 proc sql; describe view my_db.diagnosis_view; quit; Im Log Fenster: NOTE: SQL view MY_DB.DIAGNOSIS_VIEW is defined as: select * from MY_DB.ADMITS where primdx like '410%'; Data Definition Language Marianne Weires C050 Page 14 • PROC SQL Tabellen und Views Input für weitere SAS Prozeduren • Vermeiden von order by innerhalb einer View Æ Daten werden bei jeder Referenzierung auf die View sortiert • wenn eine View oft referenziert wird ÆTabelle erstellen • wenn sich die Struktur von Tabellen ändert Æ besser keine Views benutzen SAS-Treff am URZ Heidelberg 7 Einführung in PROC SQL 10.07.2008 Data Definition Language Marianne Weires C050 Page 15 • Erstellen von Indizes (CREATE) - einfacher Index (simpler index) über eine einzige Spalte * proc sql; create index id /* indexname = spaltenname! */ on my_db.patients(id); quit; NOTE: Simple index id has been defined. - zusammengesetzter Index (composite index) über mehrere Spalten * proc sql; create index name on my_db.patients(lastname,firstname); quit; NOTE: Composite index name has been defined. Marianne Weires C050 Data Definition Language Page 16 SAS-Treff am URZ Heidelberg 8 Einführung in PROC SQL 10.07.2008 Data Definition Language Marianne Weires C050 Page 17 • Index erstellen mit SAS 1) mit PROC DATASETS proc datasets library modify patients; index delete id; /* index create id; /* index create name = run; = my_db; index löschen */ simpler index */ (lastname firstname); /* composite index */ 2) mit DATA Set Option data my_db.patients(index = (lastname)); set my_db.patients; run; Data Definition Language Marianne Weires C050 Page 18 • Löschen von Tabellen/Views/Indizes (DROP) proc sql; drop table my_db.patients; quit; proc sql; drop view diagnosis_view; quit; proc sql; drop index name on my_db.patients; drop index id on my_db.patients; quit; SAS-Treff am URZ Heidelberg 9 Einführung in PROC SQL 10.07.2008 Data Manipulation Language Marianne Weires C050 Page 19 • Struktur von SQL Abfragen (SELECT) 1 select 2 from 3 where 4 group by 5 having 6 order by optional Data Manipulation Language Marianne Weires C050 Page 20 - alle Spalten auswählen: * proc sql; select * from my_db.patients; quit; proc sql outobs = 5; /* nur 5 beobachtungen */ select * from my_db.patients; quit; SAS-Treff am URZ Heidelberg 10 Einführung in PROC SQL 10.07.2008 Data Manipulation Language Marianne Weires C050 Page 21 - Spalten auswählen: * proc sql; select firstname,lastname from my_db.patients; /* ausgabereihenfolge wie angegeben */ quit; proc sql; select * from my_db.doctors(drop = HOSPADM); /* DATA step option */ quit; Data Manipulation Language Marianne Weires C050 Page 22 - Duplikate eliminieren: * proc sql; select distinct * /* verschieden in ALLEN spalten */ from my_db.patients; quit; oder mit SAS: proc sort data = my_db.patients out = my_db.patients_nodup noduplicates; by lastname; run; SAS-Treff am URZ Heidelberg 11 Einführung in PROC SQL 10.07.2008 Data Manipulation Language Marianne Weires C050 Page 23 - Duplikate eliminieren: proc sql; select distinct lastname /* verschieden in nur einer spalte */ from my_db.patients; quit; LASTNAME Bauer Betz Casper Hemmingw Humboldt Kampinsk Lessing Mayer … Marianne Weires C050 Data Manipulation Language Page 24 oder mit SAS: proc sort data = my_db.patients out = my_db.patients_nodup nodupkey; by lastname; run; Obs ID SEX PRIMMD BIRTHDTE LASTNAME FIRSTNAME 1 10 2 2322 14Jan1965 Bauer Jürgen 2 1 2 1972 14OCT193 Betz Stefan 3 6 1 2322 12APR195 Casper Martha 4 14 2 8034 14MAY193 Hemmingw Greg 5 15 1 4003 10DEC193 Humboldt Karla 6 3 2 4002 14JAN193 Kampinsk Igor 7 5 1 1972 12FEB196 Lessing Katrin .. SAS-Treff am URZ Heidelberg zusätzliche Spalten … … … … … … 12 Einführung in PROC SQL 10.07.2008 Data Manipulation Language Marianne Weires C050 Page 25 - Anlegen von neuen Spalten (CASE Anweisung): proc sql; create table my_db.bp as select pt_id, bp_sys label = 'Blutdruck systolisch (mmHg)', bp_dia label = 'Blutdruck diastolisch (mmHg)', case when (bp_sys >= 140) and (bp_dia >= 90) then 'hoch' else 'normal' end as Blutdruck /* weitere case anweisungen */ from my_db.admits; quit; PT_ID Blutdruck systolisch (mmHg) Blutdruck diastolisch (mmHg) 1 188 85 1 230 101 ... ... ... Blutdruck normal Hoch ... Data Manipulation Language Marianne Weires C050 Page 26 - Auswahl von Beobachtungen (WHERE): proc sql; select * from my_db.bp where blutdruck = 'hoch'; quit; - einige Operatoren: SAS-Treff am URZ Heidelberg 13 Einführung in PROC SQL 10.07.2008 Data Manipulation Language Marianne Weires C050 Page 27 - Duplikate ausgeben (GROUP BY, HAVING): proc sql; create table my_db.doctors_dup as select md_id, lastname, hospadm as hospital, count(*) as anzahl from my_db.doctors group by md_id having count(*) >= 2 order by lastname; quit; MD_ID hospital anzahl 7803 Avitable LASTNAME 3 2 7803 Avitable 2 2 1972 Fitzhugh 1 2 1972 Fitzhugh 2 2 3274 Hanratty 3 3 3274 Hanratty 2 3 3274 Hanratty 1 3 … … … … Data Manipulation Language Marianne Weires C050 Page 28 oder mit SAS: proc sort data = my_db.doctors; by md_id; run; data my_db.doctors_dup; set my_db.doctors (rename = (hospadm=hospital)); by md_id; if not (first.md_id and last.md_id); run; proc sort data = my_db.doctors_dup; by lastname; run; SAS-Treff am URZ Heidelberg 14 Einführung in PROC SQL 10.07.2008 Data Manipulation Language Marianne Weires C050 Page 29 • Einfügen von Beobachtungen (INSERT) 1) mit values statement proc sql; insert into my_db.doctors values (1111, 'Harald', 4) values (1111, 'Harald', 2); quit; Data Manipulation Language Marianne Weires C050 Page 30 2) mit set statement proc sql; insert into my_db.doctors set md_id = 1111, lastname = 'Harald', hospadm = 4 set md_id = 1111, lastname = 'Harald', hospadm = 2; quit; 3) aus einer Abfrage: proc sql; insert into my_db.doctors_new select * from my_db.doctors; /* werden angehangen */ quit; SAS-Treff am URZ Heidelberg 15 Einführung in PROC SQL 10.07.2008 Data Manipulation Language Marianne Weires C050 Page 31 • Ändern von Beobachtungen (UPDATE) proc sql; update my_db.hospital set nbeds = nbeds * 10; /* ALLE einträge geändert! */ quit; Æ einschränken mit where Data Manipulation Language Marianne Weires C050 Page 32 • Löschen von Beobachtungen (DELETE) proc sql; delete from my_db.doctors; /* ALLE einträge gelöscht! */ quit; Æ einschränken mit where proc sql; delete from my_db.doctors where md_id = 1111; quit; SAS-Treff am URZ Heidelberg 16 Einführung in PROC SQL 10.07.2008 Data Manipulation Language Marianne Weires C050 Page 33 • Verknüpfen von mehreren Tabellen (JOIN) - vergleichbar zu merge - Daten werden horizontal miteinander verknüpft Bespieldaten: Tabelle Two Tabelle One X Y 1 2 2 C050 2 3 9 Marianne Weires Z 4 2 4 A B 1 2 2 4 3 4 5 6 7 8 Data Manipulation Language Page 34 proc sql; select * from my_db.one, my_db.two; /* keine where-bedingung angegeben */ quit; X Y Z A B 1 2 2 1 2 1 2 2 2 4 1 2 2 3 4 1 2 2 5 6 1 2 2 7 8 2 3 4 1 2 2 3 4 2 4 2 3 4 3 4 2 3 4 5 6 2 3 4 7 8 9 2 4 1 2 3x5 Einträge Æ Kartesisches Produkt … … … … … SAS-Treff am URZ Heidelberg 17 Einführung in PROC SQL 10.07.2008 Data Manipulation Language Marianne Weires C050 Page 35 Kartesisches Produkt: vollständige Kombination von Datensätzen miteinander Æ sehr rechenintensiv Æ Im SAS Log Fenster: 1976 proc sql; 1977 select * 1978 from my_db.one, my_db.two; NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized. 1979 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.03 seconds cpu time 0.00 seconds Marianne Weires C050 Data Manipulation Language Page 36 (Fast) immer will man nur eine Untermenge des kartesischen Produktes Inner Join Right Join Left Join Full Join Outer Joins SAS-Treff am URZ Heidelberg 18 Einführung in PROC SQL 10.07.2008 Data Manipulation Language Marianne Weires C050 Page 37 • Inner Join - Schnittmenge proc sql; select * from my_db.one as a, my_db.two as b where a.X = b.A; quit; oder * proc sql; select * from my_db.one as a inner join my_db.two as b on a.X = b.A; quit; Marianne Weires C050 X Y Z A B 1 2 2 1 2 2 3 4 2 4 Data Manipulation Language Page 38 oder mit SAS: proc sort data = my_db.one; by X; run; proc sort data = my_db.two; by A; run; data my_db.inner_join; merge my_db.one (in = one) my_db.two (in = two rename = (A = X)); by X; if one and two; run; SAS-Treff am URZ Heidelberg 19 Einführung in PROC SQL 10.07.2008 Data Manipulation Language Marianne Weires C050 Page 39 • Outer Joins Als Ergebnis wird eine Tabelle ausgegeben, die - alle gemeinsamen Beobachtungen enthält und - zusätzlich die restlichen Zeilen der linken (left join) bzw. der rechten Tabelle (right join) Data Manipulation Language Marianne Weires C050 Page 40 • Left Join linke Tabelle ist Mastertabelle (d.h. alle Zeilen der linken Tabelle mit den entsprechenden Zeilen der rechten Tabelle) * proc sql; select * from my_db.one as a left join my_db.two as b on a.X = b.A; quit; SAS-Treff am URZ Heidelberg X Y Z A B 1 2 2 1 2 2 3 4 2 4 9 2 4 . . in linker und rechter Tabelle nur in linker Tabelle 20 Einführung in PROC SQL 10.07.2008 Data Manipulation Language Marianne Weires C050 Page 41 oder mit SAS: /* left join */ data my_db.left_join; merge my_db.one (in = one) my_db.two (rename = (A = X)); by X; if one; run; Marianne Weires C050 Data Manipulation Language Page 42 • Right Join rechte Tabelle ist Mastertabelle (d.h. alle Zeilen der rechten Tabelle mit den entsprechenden Zeilen der linken Tabelle) * proc sql; select * from my_db.one as a right join my_db.two as b on a.X = b.A; quit; SAS-Treff am URZ Heidelberg X Y Z A B 1 2 2 1 2 2 3 4 2 4 . . . 3 4 . . . 5 6 . . . 7 8 21 Einführung in PROC SQL 10.07.2008 Data Manipulation Language Marianne Weires C050 Page 43 oder mit SAS: /* right join */ data my_db.right_join; merge my_db.one my_db.two (in = two rename = (A = X)); by X; if two; run; Data Manipulation Language Marianne Weires C050 Page 44 • Full Join Kombination aus left join und right join * proc sql; select * from my_db.one as a full join my_db.two as b on a.X = b.A; quit; SAS-Treff am URZ Heidelberg X Y Z A B 1 2 2 1 2 2 3 4 2 4 . . . 3 4 . . . 5 6 . . . 7 8 9 2 4 . . aus rechter Tabelle aus linker Tabelle 22 Einführung in PROC SQL 10.07.2008 Data Manipulation Language Marianne Weires C050 Page 45 oder mit SAS: /* full join */ data my_db.full_join; merge my_db.one my_db.two (rename = (A = X)); by X; run; Bemerkungen: - mit PROC SQL kein sortieren vorher notwenig - Variablen können unterschiedliche Namen haben (kein rename notwendig) Data Manipulation Language Marianne Weires C050 Page 46 • Geschachtelte Abfragen (Subqueries) Eine Tabelle in einem Join kann wiederum eine SQL Abfrage sein Beispiel: „Wir wollen den Namen von jedem Patienten der eingeliefert wurde“ * proc sql; select id, lastname, firstname from my_db.patients where id in (select distinct pt_id from my_db.admits); quit; Æ diese Abfrage wäre auch mit einem inner join möglich SAS-Treff am URZ Heidelberg 23 Einführung in PROC SQL 10.07.2008 Data Manipulation Language Marianne Weires C050 Page 47 Beispiel: „Wir wollen zu jedem Patient alle Aufnahmen herausfinden, die von seinem Hausarzt durchgeführt wurden. Dabei interessiert uns der Patientenname, Patientenid, Aufnahmedatum, Arztname“ Æ Wir brauchen Informationen aus 3 Tabellen: my_db.admits, my_db.patients, my_db.doctors Data Manipulation Language Marianne Weires C050 Page 48 my_db.admits: Daraus bekommen wir alle Patienten, die eingeliefert wurden (nicht jeder der 15 Patienten wurde eingeliefert) und den Arzt, der eingeliefert hat PT_ID ADMDATE DISDATE MD_ID HOSP_ID DEST BP_SYS BP_DIA PRIMDX my_db.patients: Daraus bekommen wir Patientennamen und ids und die Hausarztid ID SEX PRIMMD BIRTHDTE LASTNAME FIRSTNAME my_db.doctors: Daraus bekommen wir die Arztid und den Arztnamen MD_ID SAS-Treff am URZ Heidelberg LASTNAME HOSPADM 24 Einführung in PROC SQL 10.07.2008 Data Manipulation Language Marianne Weires C050 Page 49 proc sql; create table my_db.adm_prim as select pt_id, admdate, pat.lastname as patname, doc.md_id, doc.lastname as doclastname from my_db.admits as adm inner join my_db.patients as pat on (adm.pt_id = pat.id) and (adm.md_id = pat.primmd) inner join (select distinct md_id, lastname from my_db.doctors) as doc on (adm.md_id = doc.md_id) order by adm.pt_id, admdate; quit; Data Manipulation Language Marianne Weires C050 Page 50 proc sql; create table my_db.adm_prim as select pt_id, admdate, pat.lastname as patname, doc.md_id, doc.lastname as doclastname from my_db.admits as adm inner join my_db.patients as pat on (adm.pt_id = pat.id) and (adm.md_id = pat.primmd) inner join (select distinct md_id, lastname from my_db.doctors) as doc on (adm.md_id = doc.md_id) order by adm.pt_id, admdate; quit; Erste Abfrage liefert nur Einträge für Patienten, die eingeliefert worden sind und der Einweisungsarzt der Hausarzt des Patienten ist SAS-Treff am URZ Heidelberg 25 Einführung in PROC SQL 10.07.2008 Data Manipulation Language Marianne Weires C050 Page 51 proc sql; create table my_db.adm_prim as select pt_id, admdate, pat.lastname as patname, doc.md_id, doc.lastname as doclastname from my_db.admits as adm inner join my_db.patients as pat on (adm.pt_id = pat.id) and (adm.md_id = pat.primmd) inner join (select distinct md_id, lastname from my_db.doctors) as doc on (adm.md_id = doc.md_id) order by adm.pt_id, admdate; quit; Zweite Abfrage liefert nur unterschiedliche Einträge für jeden Arzt, der Einlieferungsarzt ist und gleichzeitig Hausarzt Marianne Weires C050 Data Manipulation Language Page 52 SAS-Treff am URZ Heidelberg PT_ID ADMDATE patname 1 12APR1999 Betz MD_ID 1972 doclastname Fitzhugh 5 10APR1999 Lessing 1972 Fitzhugh 5 10MAR1999 Lessing 1972 Fitzhugh 5 19JAN1999 Lessing 1972 Fitzhugh 7 08SEP1996 Schreibe 3274 Hanratty 7 28JUL1996 Schreibe 3274 Hanratty 10 30NOV1998 Bauer 2322 MacArthu 15 17AUG1997 Humboldt 4003 Colanton 15 25MAY1997 Humboldt 4003 Colanton 26 Einführung in PROC SQL 10.07.2008 Marianne Weires C050 Page 53 In SAS: data my_db.adm_prim1 (drop = primmd); merge my_db.admits (in = adm keep = pt_id admdate md_id) my_db.patients (in = pat keep = id lastname primmd rename = (id = pt_id)); by pt_id; if adm and pat and (md_id = primmd); run; proc sort data = my_db.doctors; by md_id; run; data my_db.doctors_nodup; set my_db.doctors(keep = md_id lastname); by md_id; if first.md_id; run; proc sort data = my_db.adm_prim1; by md_id; run; data my_db.adm_prim; merge my_db.adm_prim1 (in = prim rename = (lastname = patname)) my_db.doctors_nodup (rename = (lastname = docname)); by md_id; if prim; run; Data Manipulation Language Marianne Weires C050 Page 54 • Verknüpfung von mehreren Abfragen (Set Operatoren) - vergleichbar mit set - Daten werden vertikal miteinander verknüpft - grundsätzlich Verknüpfung nach Spaltenposition - z.T. kein direktes Gegenstück in SAS Bespieldaten: Tabelle Two Tabelle One Y 1 2 9 SAS-Treff am URZ Heidelberg A A B 2 1 2 3 2 4 3 4 5 6 7 8 2 27 Einführung in PROC SQL 10.07.2008 Data Manipulation Language Marianne Weires C050 Page 55 UNION EXCEPT INTERSECT OUTER UNION Spaltenreihenfolge u.U. wichtig Modifikation durch: ALL - Doppelte Zeilen werden nicht unterdrückt. nicht mit OUTER UNION verwendbar, da hier bereits alle Zeilen ausgegeben werden CORRESPONDING (CORR) - Spalten mit gleichem Namen werden überlagert (gleiche Spaltennamen erkannt) Data Manipulation Language Marianne Weires C050 Page 56 • Union - Alle Zeilen aus Tabellen ausgegeben ohne doppelte Zeilen (Æ sonst ALL). - Verknüpfung der Spalten entsprechend der Position, d.h. Variablen gleichen Namens nicht erkannt (Æ sonst CORR) - resultierenden Spalten erhalten die Namen der ersten Tabelle * proc sql; select * from my_db.one union select * from my_db.two; quit; SAS-Treff am URZ Heidelberg A Y 1 2 2 3 2 4 3 4 5 6 7 8 9 2 28 Einführung in PROC SQL 10.07.2008 Data Manipulation Language Marianne Weires C050 Page 57 * A proc sql; select * from my_db.one union corr /* verknüpfung über gl. spaltennamen */ select * from my_db.two; quit; 1 2 3 5 7 * proc sql; select * from my_db.one union all /* doppelte Zeilen ausgeben */ select * from my_db.two; quit; Y A 1 2 2 3 9 2 1 2 2 4 3 4 5 6 7 8 Data Manipulation Language Marianne Weires C050 Page 58 • Except - Ergebnisse aus one, jedoch nicht in two - für doppelte Zeilen ALL verwenden. - Vergleich anhand gemeinsamer Variablen mit CORR proc sql; select * from my_db.one except select * from my_db.two; quit; SAS-Treff am URZ Heidelberg Y A 2 3 9 2 29 Einführung in PROC SQL 10.07.2008 Data Manipulation Language Marianne Weires C050 Page 59 • Intersect nur Beobachtungen, die in beiden Dateien die gleichen Ausprägungen in den angegebenen Spalten haben proc sql; select * from my_db.one intersect select * from my_db.two; quit; Y A 1 2 Data Manipulation Language Marianne Weires C050 Page 60 • Outer Union proc sql; select * from my_db.one outer union select* from my_db.two; quit; SAS-Treff am URZ Heidelberg Y A A B 1 2 . . 2 3 . . 9 2 . . . . 1 2 . . 2 4 . . 3 4 . . 5 6 . . 7 8 30 Einführung in PROC SQL 10.07.2008 Data Manipulation Language Marianne Weires C050 Page 61 proc sql; select * from my_db.one outer union corr /* gl. spaltennamen erkannt */ select * from my_db.two; quit; Y A B 1 2 . 2 3 . 9 2 . . 1 2 . 2 4 . 3 4 . 5 6 . 7 8 Performanz und PROC SQL Marianne Weires C050 Page 62 • Tipps für performante SQL Abfragen: - where Bedingung spezifizieren - eher join als eine subquery verwenden - auf order by in Views verzichten - Indexieren SAS-Treff am URZ Heidelberg 31 Einführung in PROC SQL 10.07.2008 Performanz und PROC SQL Marianne Weires C050 Page 63 • Der Index - Ohne Index wird eine Tabelle sequentiell gelesen - ein Index ist eine geordnete Struktur (z.B. Baum) mit Verweis auf Einträge Æ Ordnung verkürzt also die Suche nach einem bestimmten Datensatz - ein Index kann die Verknüpfung von Tabellen (joins) beschleunigen Sinnvoll einen Index zu erstellen: - bestimmte Spalten immer wieder in joins genutzt - Spaltenwerte viele untersch. Werte besitzen (einzelne Datensätze gut diskriminieren) (z.B. patienten id ↔ geschlecht) - Teilmenge der gesamten Daten benötigt wird Marianne Weires C050 Performanz und PROC SQL Page 64 Beispiel: Indexstruktur - binärer Baum - zyklenfreie Graphstruktur - besteht aus Knoten verbunden durch gerichtete Kanten SAS-Treff am URZ Heidelberg record identifier lastname firstname 001 Betz Stefan 002 Polak Edgar 003 Kampinski Igor 004 Mayer Karl 005 Lessing Katrin 006 Casper Martha 007 Schreiber Tobias 008 Schwartkopf Walter 009 Naipaul Victor 010 Bauer Jürgen 011 Voltus Jean 012 Bauer Alberta 013 Shakes William 014 Hemmingway Greg 015 Humboldt Karla 32 Einführung in PROC SQL 10.07.2008 Marianne Weires C050 Performanz und PROC SQL Page 65 Abfrage: proc sql; select name, vorname from my_db.patient where lastname = 'Humboldt' and firstname = 'Karla'; quit; Ohne Index sequentielle Suche in der ungeordneten Tabelle Æ 15 Schritte zum Finden Index über die Spalten Name und Vorname Æ binärer Baum wird angelegt Marianne Weires C050 Voltus Jean, 011 Page 66 Shakes William, 013 Schwartzkopf Walter, 008 Schreiber Tobias, 007 Polak Edgar, 002 Naipaul Victor, 009 Mayer Karl, 004 Lessing Katrin, 005 Kampinski Iger, 003 Humboldt Karla, 015 Hemmingway Greg, 014 Casper Martha, 006 Betz Stefan, 001 Bauer Jürgen, 010 Bauer Alberta, 012 SAS-Treff am URZ Heidelberg 33 Einführung in PROC SQL 10.07.2008 Marianne Weires C050 Voltus Jean, 011 Page 67 Shakes William, 013 Schwartzkopf Walter, 008 Schreiber Tobias, 007 Polak Edgar, 002 Naipaul Victor, 009 Mayer Karl, 004 Lessing Katrin, 005 Kampinski Iger, 003 Humboldt Karla, 015 Hemmingway Greg, 014 Casper Martha, 006 Betz Stefan, 001 Bauer Jürgen, 010 Bauer Alberta, 012 Marianne Weires C050 Voltus Jean, 011 Page 68 Shakes William, 013 Schwartzkopf Walter, 008 Schreiber Tobias, 007 Polak Edgar, 002 Naipaul Victor, 009 Mayer Karl, 004 Lessing Katrin, 005 Kampinski Iger, 003 Humboldt Karla, 015 Hemmingway Greg, 014 Casper Martha, 006 Betz Stefan, 001 Bauer Jürgen, 010 Bauer Alberta, 012 SAS-Treff am URZ Heidelberg 34 Einführung in PROC SQL 10.07.2008 Marianne Weires C050 Voltus Jean, 011 Page 69 Shakes William, 013 Schwartzkopf Walter, 008 Schreiber Tobias, 007 Polak Edgar, 002 Naipaul Victor, 009 Mayer Karl, 004 Lessing Katrin, 005 Kampinski Iger, 003 Humboldt Karla, 015 Hemmingway Greg, 014 Casper Martha, 006 Betz Stefan, 001 Bauer Jürgen, 010 Bauer Alberta, 012 Æ 3 Schritte zum Finden der internen Adresse und 1 Schritt zum Nachschauen des Datensatzes in der Tabelle. Performanz und PROC SQL Marianne Weires C050 Page 70 • Manche Knoten sind durch das Umorganisieren schlechter als vorher platziert (z.B. „Betz Stefan“) Æ Im Durchschnitt verringert sich aber die Anzahl der Vergleiche • Blattknoten möglichst die gleiche Distanz zum Wurzelknoten Æ balancierter Baum Æ nach Hinzufügen/Löschen Baum wieder ausbalancieren Æ B-Bäume in DB (balancierte Bäume Æ weniger Zugriffe) • zusätzlicher Speicherverbrauch für einen Index Æ jedoch werden die meisten Datenbanken viel häufiger gelesen als geschrieben • Indexierte Daten müssen vor einer by Verarbeitung nicht mehr sortiert werden • Indizes werden beim nachfolgenden sortieren ungültig (force Option setzen) proc sort data = my_db.two force; by A; run; SAS-Treff am URZ Heidelberg 35 Einführung in PROC SQL 10.07.2008 Performanz und PROC SQL Marianne Weires C050 Page 71 • Beispiel: proc sql; select id, sex, couple_id from spouses.couples_02 where id not in (select id from spouses.all); quit; Ohne Index: NOTE: PROCEDURE SQL used (Total process time): real time 16:39.15 cpu time 5:40.26 Mit Index (id): 476 create index id 477 on spouses.couples_02(id); real time 2:59.75 cpu time 11.39 seconds 478 479 create index id 480 on spouses.all; real time 5:18.38 cpu time 10.93 seconds NOTE: SQL Statement used (Total process time): real time 3:32.08 cpu time 24.35 seconds Marianne Weires C050 Page 72 SAS und relationale Datenbanken • SAS/ACCESS Software: Schnittstelle zwischen SAS SW und einem anderen DB Management System Zugriffsmöglichkeiten: libname Anweisung - einer Datenbank eine SAS library zuweisen - Zugriff auf DB mit DATA Step Programmierung - Optimierung von joins und anderen Funktionen (Indizes der Datenbank genutzt) libname my_lib mysql user = testuser password = testpass database = mysqldb server = mysqlserv port = 9876; proc print data = my_lib.employees; where dept = 'CSR010';run; SAS-Treff am URZ Heidelberg PROC SQL Pass-Through Facility - SQL Erweiterungen der jeweiligen DB können genutzt werden - Optimierung von joins und anderen Funktionen (Indizes der Datenbank genutzt) proc sql; connect to mysql (user = ...); select * from connection to mysql (select * from customers); disconnect from mysql; quit; 36 Einführung in PROC SQL 10.07.2008 Zusammenfassung Marianne Weires C050 Page 73 • Nicht behandelt: - Aggregierungsfunktionen - Makrovariablen und SQL • Fazit: - Kompaktere Anweisungen mit PROC SQL - Set Operatoren Æ kein direktes Gegenstück für UNION, EXCEPT, INTERSECT in SAS - Keine statistischen Auswertungen mit PROC SQL - SQL ist DER Standard für relationale Datenbanksysteme Marianne Weires C050 Literaturliste Page 74 DATA Step vs. PROC SQL: What’s a neophyte to do? Craig Dickstein, (Paper 61-26) Proceedings of the 12th Annual NorthEast SAS Users Group Conference, Washington, DC, 1999. An Introduction to PROC SQL, Rosalind K. Gusinow and J. Michael Miscisin (Handson Workshops) Undocumented and Hard-to-find SQL Features Kirk Paul Lafler (Paper 19-28) SAS Institute Inc., 2004. SAS/ACCESS 9.1.2 Supplement for MySQL Frequently Asked Questions about SAS Indexes (Paper 008-30) An Introduction to SQL in SAS Pete Lund (Paper 257-30) * SAS Institute Inc., 2004. SAS9.1 SQL Procedure User’s Guide. * PROC SQL for DATA Step Die-Hards, Williams, C.S. (manche Beispiele) SQL SET OPERATORS: SO HANDY VENN YOU NEED THEM, Howard Schreier (PAPER 242-31) * Creating and Exploiting SAS Indexes Michael A. Raithel (Paper 123-29) Top Ten Reasons to Use PROC SQL Weiming Hu (Paper 042-29) SAS-Treff am URZ Heidelberg * * 37 Einführung in PROC SQL Marianne Weires C050 10.07.2008 Einführung in PROC SQL Page 75 Vielen Dank für die Aufmerksamkeit! Bei weiteren Fragen: [email protected] SAS-Treff am URZ Heidelberg 38
