View
236
Download
0
Category
Preview:
Citation preview
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 1
Einführung in PROC SQL
Marianne Weires
Page 2
Marianne WeiresC050 Überblick
• Was ist SQL und PROC SQL?
• PROC SQL und SAS DATA Step
• Data Definition Language:
• Data Manipulation Language:
Erstellen/Löschen von Tabellen/Views/Indizes (CREATE, DROP)
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)
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 2
Page 3
Marianne WeiresC050
Was ist SQL und PROC SQL?
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)
Page 4
Marianne WeiresC050
PROC SQL und SAS DATA Step
• Terminologie
BibliothekDatenbank
Variable Spalte BeobachtungZeileSAS data file Tabelle
SAS Term SQL Term
• Syntaxproc sql <option(s)>; /* anfang */
create table ...; /* sql befehle*/
select ...;
drop table ...;...
quit; /* ende */
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 3
Page 5
Marianne WeiresC050
PROC SQL und SAS DATA Step
• Kategorien von Befehlen in SQL
Data Definition Language (DDL)Definition des Datenbankschemas
Data Control Language (DCL)Rechteverwaltung
Data Manipulation Language (DML)Datenmanipulation
Page 6
Marianne WeiresC050
PROC SQL und SAS DATA Step
• Kategorien von Befehlen
Data Definition Language (DDL)Definition des Datenbankschemas
Data Control Language (DCL)Rechteverwaltung
Data Manipulation Language (DML)Datenmanipulation
• Kategorien von Befehlen in SQL
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 4
Page 7
Marianne WeiresC050
Beispieldatensätze
Relation
Page 8
Marianne WeiresC050
Beispieldatensätze
my_db.admits
...........................
431407493232215MAR199815MAR19983
428.49418592327412JUN199806JUN19981
813.97817022327419SEP199910SEP19991
428.210123011197225APR199912APR19991
410.08518811327408FEB199907FEB19991
PRIMDXBP_DIABP_SYSDESTHOSP_IDMD_IDDISDATEADMDATEPT_ID
my_db.patients
..................KarlaHumboldt31AUG1937197225
GregHemmingw25MAY1916400314
MarthaCasper02JUL1918198913
StefanBetz17MAR1925197222
JürgenBauer10AUG1938197211
FIRSTNAMELASTNAMEBIRTHDTEPRIMMDSEXID
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 5
Page 9
Marianne WeiresC050
Beispieldatensätze
my_db.hospital
...............11025NewMitfoCity Hospital5
1448DerbyvilCommunity Hospital4
31176WestMitfVeteran’s Administration3
2645NorthMitOur Lady of Charity2
1841NewMitfoBig University Hospital1
TYPENBEDSTOWNHOSPNAMEHOSP_ID
my_db.doctors
.........
4Rosenberg2998
3Mac Arthur2322
1Mac Arthur2322
2Fitzhugh1972
1Fitzhugh1972
HOSPADMLASTNAMEMD_ID
Page 10
Marianne WeiresC050
Data Definition Language
• 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;
*
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 6
Page 11
Marianne WeiresC050
Data Definition Language
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;
*
Page 12
Marianne WeiresC050
Data Definition Language
- 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 asselect * from my_db.admitswhere primdx like '410%'; /* primary diagnosis */
select * from my_db.diagnosis_view;quit;
• Erstellen von Views (CREATE)
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 7
Page 13
Marianne WeiresC050
Data Definition Language
proc sql;describe view my_db.diagnosis_view;
quit;
NOTE: SQL view MY_DB.DIAGNOSIS_VIEW is defined as:
select *from MY_DB.ADMITSwhere primdx like '410%';
Im Log Fenster:
Page 14
Marianne WeiresC050
Data Definition Language
• 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
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 8
Page 15
Marianne WeiresC050
Data Definition Language
• 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;
proc sql;create index nameon my_db.patients(lastname,firstname);
quit;
- zusammengesetzter Index (composite index) über mehrere Spalten
*
*
NOTE: Simple index id has been defined.
NOTE: Composite index name has been defined.
Page 16
Marianne WeiresC050
Data Definition Language
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 9
Page 17
Marianne WeiresC050
Data Definition Language
• Index erstellen mit SAS
proc datasets library = my_db;modify patients;index delete id; /* index löschen */index create id; /* simpler index */index create name = (lastname firstname); /* composite index */
run;
1) mit PROC DATASETS
data my_db.patients(index = (lastname));set my_db.patients;
run;
2) mit DATA Set Option
Page 18
Marianne WeiresC050
Data Definition Language
• 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;
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 10
Page 19
Marianne WeiresC050
Data Manipulation Language
• Struktur von SQL Abfragen (SELECT)
1 select2 from3 where4 group by5 having6 order by
optional
Page 20
Marianne WeiresC050
Data Manipulation Language
proc sql;select * from my_db.patients;
quit;
proc sql outobs = 5; /* nur 5 beobachtungen */select * from my_db.patients;
quit;
- alle Spalten auswählen:
*
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 11
Page 21
Marianne WeiresC050
Data Manipulation Language
proc sql;select firstname,lastnamefrom my_db.patients; /* ausgabereihenfolge wie angegeben */
quit;
proc sql;select * from my_db.doctors(drop = HOSPADM); /* DATA step option */
quit;
- Spalten auswählen:
*
Page 22
Marianne WeiresC050
Data Manipulation Language
- Duplikate eliminieren:
proc sql; select distinct * /* verschieden in ALLEN spalten */from my_db.patients;
quit;
proc sort data = my_db.patientsout = my_db.patients_nodup noduplicates;by lastname;
run;
oder mit SAS:
*
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 12
Page 23
Marianne WeiresC050
Data Manipulation Language
- Duplikate eliminieren:
proc sql; select distinct lastname /* verschieden in nur einer spalte */from my_db.patients;
quit;
…Mayer
Lessing
Kampinsk
Humboldt
Hemmingw
Casper
Betz
Bauer
LASTNAME
Page 24
Marianne WeiresC050
Data Manipulation Language
proc sort data = my_db.patientsout = my_db.patients_nodup nodupkey;by lastname;
run;
oder mit SAS:
………………..KatrinLessing12FEB1961972157
IgorKampinsk14JAN1934002236
KarlaHumboldt10DEC19340031155
GregHemmingw14MAY19380342144
MarthaCasper12APR1952322163
StefanBetz14OCT1931972212
JürgenBauer14Jan196523222101
FIRSTNAMELASTNAMEBIRTHDTEPRIMMDSEXIDObs
zusätzliche Spalten
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 13
Page 25
Marianne WeiresC050
Data Manipulation Language
proc sql;create table my_db.bp as
select pt_id, bp_sys label = 'Blutdruck systolisch (mmHg)', bp_dia label = 'Blutdruck diastolisch (mmHg)',
casewhen (bp_sys >= 140) and (bp_dia >= 90) then 'hoch'else 'normal'
end as Blutdruck/* weitere case anweisungen */
from my_db.admits;quit;
- Anlegen von neuen Spalten (CASE Anweisung):
............Hoch1012301
normal851881
Blutdruck
Blutdruck diastolisch
(mmHg)
Blutdruck systolisch(mmHg)PT_ID
Page 26
Marianne WeiresC050
Data Manipulation Language
proc sql;select *from my_db.bpwhere blutdruck = 'hoch';
quit;
- Auswahl von Beobachtungen (WHERE):
- einige Operatoren:
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 14
Page 27
Marianne WeiresC050
Data Manipulation Language
- 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.doctorsgroup by md_idhaving count(*) >= 2
order by lastname; quit; …………
31Hanratty3274
32Hanratty3274
33Hanratty3274
22Fitzhugh1972
21Fitzhugh1972
22Avitable7803
23Avitable7803
anzahlhospitalLASTNAMEMD_ID
Page 28
Marianne WeiresC050
Data Manipulation Language
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;
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 15
Page 29
Marianne WeiresC050
Data Manipulation Language
• Einfügen von Beobachtungen (INSERT)
proc sql;insert into my_db.doctorsvalues (1111, 'Harald', 4)values (1111, 'Harald', 2);
quit;
1) mit values statement
Page 30
Marianne WeiresC050
Data Manipulation Language
proc sql;insert into my_db.doctorsset md_id = 1111,
lastname = 'Harald',hospadm = 4
set md_id = 1111,lastname = 'Harald',hospadm = 2;
quit;
2) mit set statement
proc sql;insert into my_db.doctors_new
select * from my_db.doctors; /* werden angehangen */quit;
3) aus einer Abfrage:
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 16
Page 31
Marianne WeiresC050
Data Manipulation Language
• Ändern von Beobachtungen (UPDATE)
proc sql;update my_db.hospitalset nbeds = nbeds * 10; /* ALLE einträge geändert! */
quit;
einschränken mit where
Page 32
Marianne WeiresC050
Data Manipulation Language
• Löschen von Beobachtungen (DELETE)
proc sql;delete from my_db.doctors; /* ALLE einträge gelöscht! */
quit;
proc sql;delete from my_db.doctorswhere md_id = 1111;
quit;
einschränken mit where
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 17
Page 33
Marianne WeiresC050
Data Manipulation Language
• Verknüpfen von mehreren Tabellen (JOIN)
- vergleichbar zu merge- Daten werden horizontal miteinander verknüpft
429
432
221
ZYX
Tabelle One
87
65
43
42
21
BA
Tabelle Two
Bespieldaten:
Page 34
Marianne WeiresC050
Data Manipulation Language
proc sql;select * from my_db.one, my_db.two; /* keine where-bedingung angegeben */
quit;
……………
21429
87432
65432
43432
42432
21432
87221
65221
43221
42221
21221
BAZYX 3x5 Einträge
Kartesisches Produkt
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 18
Page 35
Marianne WeiresC050
Data Manipulation Language
Kartesisches Produkt:
vollständige Kombination von Datensätzen miteinander
sehr rechenintensiv
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 secondscpu time 0.00 seconds
Im SAS Log Fenster:
Page 36
Marianne WeiresC050
Data Manipulation Language
(Fast) immer will man nur eine Untermenge des kartesischen Produktes
Inner Join Right Join Left Join Full Join
Outer Joins
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 19
Page 37
Marianne WeiresC050
Data Manipulation Language
• Inner Join
proc sql;select *from my_db.one as a, my_db.two as bwhere a.X = b.A;
quit;
proc sql;select *from my_db.one as a inner join my_db.two as bon a.X = b.A;
quit; 42432
21221
BAZYX
oder
- Schnittmenge
*
Page 38
Marianne WeiresC050
Data Manipulation Language
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;
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 20
Page 39
Marianne WeiresC050
Data Manipulation Language
• 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)
Page 40
Marianne WeiresC050
Data Manipulation Language
• Left Join
proc sql;select *from my_db.one as a left join my_db.two as bon a.X = b.A;
quit;
..429
42432
21221
BAZYX
linke Tabelle ist Mastertabelle (d.h. alle Zeilen der linken Tabelle mit den entsprechenden Zeilen der rechten Tabelle)
in linker und rechter Tabelle
nur in linker Tabelle
*
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 21
Page 41
Marianne WeiresC050
Data Manipulation Language
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;
Page 42
Marianne WeiresC050
Data Manipulation Language
• Right Join
proc sql;select *from my_db.one as a right join my_db.two as bon a.X = b.A;
quit;
87...
65...
43...
42432
21221
BAZYX
rechte Tabelle ist Mastertabelle (d.h. alle Zeilen der rechten Tabelle mit den entsprechenden Zeilen der linken Tabelle)
*
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 22
Page 43
Marianne WeiresC050
Data Manipulation Language
oder mit SAS:
/* right join */data my_db.right_join;
merge my_db.onemy_db.two (in = two rename = (A = X));
by X;if two;
run;
Page 44
Marianne WeiresC050
Data Manipulation Language
• Full Join
proc sql;select *from my_db.one as a full join my_db.two as bon a.X = b.A;
quit;
..429
87...
65...
43...
42432
21221
BAZYX
aus rechter Tabelle
aus linker Tabelle
Kombination aus left join und right join
*
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 23
Page 45
Marianne WeiresC050
Data Manipulation Language
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)
Page 46
Marianne WeiresC050
Data Manipulation Language
• Geschachtelte Abfragen (Subqueries)
Eine Tabelle in einem Join kann wiederum eine SQL Abfrage sein
proc sql;select id, lastname, firstnamefrom my_db.patientswhere id in (select distinct pt_id from my_db.admits);
quit;
diese Abfrage wäre auch mit einem inner join möglich
Beispiel: „Wir wollen den Namen von jedem Patienten der eingeliefert wurde“
*
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 24
Page 47
Marianne WeiresC050
Data Manipulation Language
Wir brauchen Informationen aus 3 Tabellen: my_db.admits, my_db.patients, my_db.doctors
Beispiel: „Wir wollen zu jedem Patient alle Aufnahmen herausfinden, die von seinem Hausarzt durchgeführt wurden. Dabei interessiert uns der Patientenname, Patientenid, Aufnahmedatum, Arztname“
Page 48
Marianne WeiresC050
Data Manipulation Language
my_db.admits: Daraus bekommen wir alle Patienten, die eingeliefert wurden (nicht jeder der 15 Patienten wurde eingeliefert) und den Arzt, der eingeliefert hat
PRIMDXBP_DIABP_SYSDESTHOSP_IDMD_IDDISDATEADMDATEPT_ID
my_db.patients: Daraus bekommen wir Patientennamen und ids und die Hausarztid
FIRSTNAMELASTNAMEBIRTHDTEPRIMMDSEXID
my_db.doctors: Daraus bekommen wir die Arztid und den Arztnamen
HOSPADMLASTNAMEMD_ID
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 25
Page 49
Marianne WeiresC050
Data Manipulation Language
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 docon (adm.md_id = doc.md_id)
order by adm.pt_id, admdate;quit;
Page 50
Marianne WeiresC050
Data Manipulation Language
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 docon (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
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 26
Page 51
Marianne WeiresC050
Data Manipulation Language
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 docon (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
Page 52
Marianne WeiresC050
Data Manipulation Language
Colanton4003Humboldt25MAY199715
Colanton4003Humboldt17AUG199715
MacArthu2322Bauer30NOV199810
Hanratty3274Schreibe28JUL19967
Hanratty3274Schreibe08SEP19967
Fitzhugh1972Lessing19JAN19995
Fitzhugh1972Lessing10MAR19995
Fitzhugh1972Lessing10APR19995
Fitzhugh1972Betz12APR19991
doclastnameMD_IDpatnameADMDATEPT_ID
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 27
Page 53
Marianne WeiresC050
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 primmdrename = (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;
Page 54
Marianne WeiresC050
Data Manipulation Language
• Verknüpfung von mehreren Abfragen (Set Operatoren)
- vergleichbar mit set
- Daten werden vertikal miteinander verknüpft
Bespieldaten:
Tabelle One Tabelle Two
29
32
21
AY
87
65
43
42
21
BA
- grundsätzlich Verknüpfung nach Spaltenposition
- z.T. kein direktes Gegenstück in SAS
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 28
Page 55
Marianne WeiresC050
Data Manipulation Language
UNION EXCEPT INTERSECT OUTER UNION
Modifikation durch:
CORRESPONDING (CORR) - Spalten mit gleichem Namen werden überlagert(gleiche Spaltennamen erkannt)
ALL - Doppelte Zeilen werden nicht unterdrückt.nicht mit OUTER UNION verwendbar, da hier bereits alle Zeilen ausgegeben werden
Spaltenreihenfolge u.U. wichtig
Page 56
Marianne WeiresC050
Data Manipulation Language
• 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.oneunionselect * from my_db.two;
quit;
29
87
65
43
42
32
21
YA*
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 29
Page 57
Marianne WeiresC050
Data Manipulation Language
proc sql;select * from my_db.oneunion corr /* verknüpfung über gl. spaltennamen */select * from my_db.two;
quit;
7
5
3
2
1
A
proc sql;select * from my_db.oneunion all /* doppelte Zeilen ausgeben */select * from my_db.two;
quit;
87
65
43
42
21
29
32
21
AY
*
*
Page 58
Marianne WeiresC050
Data Manipulation Language
• 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.oneexceptselect * from my_db.two;
quit;29
32
AY
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 30
Page 59
Marianne WeiresC050
Data Manipulation Language
• Intersectnur Beobachtungen, die in beiden Dateien die
gleichen Ausprägungen in den angegebenen
Spalten haben
21
AYproc sql;select * from my_db.oneintersectselect * from my_db.two;
quit;
Page 60
Marianne WeiresC050
Data Manipulation Language
• Outer Union
proc sql;select * from my_db.oneouter unionselect* from my_db.two;
quit;
87..
65..
43..
42..
21..
..29
..32
..21
BAAY
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 31
Page 61
Marianne WeiresC050
Data Manipulation Language
proc sql;select * from my_db.oneouter union corr /* gl. spaltennamen erkannt */select * from my_db.two;
quit;
87.
65.
43.
42.
21.
.29
.32
.21
BAY
Page 62
Marianne WeiresC050
Performanz und PROC SQL
• Tipps für performante SQL Abfragen:
- where Bedingung spezifizieren- eher join als eine subquery verwenden- auf order by in Views verzichten- Indexieren
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 32
Page 63
Marianne WeiresC050
Performanz und PROC SQL
• 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
Page 64
Marianne WeiresC050
Performanz und PROC SQL
Beispiel: Indexstruktur - binärer Baum
- zyklenfreie Graphstruktur - besteht aus Knoten verbunden
durch gerichtete Kanten
KarlaHumboldt015GregHemmingway014
WilliamShakes013AlbertaBauer012JeanVoltus011
JürgenBauer010VictorNaipaul009WalterSchwartkopf008TobiasSchreiber007MarthaCasper006KatrinLessing005KarlMayer004IgorKampinski003
EdgarPolak002StefanBetz001
firstnamelastnamerecord identifier
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 33
Page 65
Marianne WeiresC050
Performanz und PROC SQL
Ohne Index sequentielle Suche in der ungeordneten Tabelle15 Schritte zum Finden
proc sql;select name, vornamefrom my_db.patientwhere lastname = 'Humboldt' and firstname = 'Karla';
quit;
Index über die Spalten Name und Vornamebinärer Baum wird angelegt
Abfrage:
Page 66
Marianne WeiresC050
Lessing Katrin, 005
Schreiber Tobias, 007
Casper Martha, 006
Shakes William, 013
Naipaul Victor, 009
Humboldt Karla, 015
Bauer Jürgen, 010
Voltus Jean, 011
Schwartzkopf Walter, 008
Polak Edgar, 002
Mayer Karl, 004
Kampinski Iger, 003
Hemmingway Greg, 014
Betz Stefan, 001
Bauer Alberta, 012
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 34
Page 67
Marianne WeiresC050
Lessing Katrin, 005
Schreiber Tobias, 007
Casper Martha, 006
Shakes William, 013
Naipaul Victor, 009
Humboldt Karla, 015
Bauer Jürgen, 010
Voltus Jean, 011
Schwartzkopf Walter, 008
Polak Edgar, 002
Mayer Karl, 004
Kampinski Iger, 003
Hemmingway Greg, 014
Betz Stefan, 001
Bauer Alberta, 012
Page 68
Marianne WeiresC050
Lessing Katrin, 005
Schreiber Tobias, 007
Casper Martha, 006
Shakes William, 013
Naipaul Victor, 009
Humboldt Karla, 015
Bauer Jürgen, 010
Voltus Jean, 011
Schwartzkopf Walter, 008
Polak Edgar, 002
Mayer Karl, 004
Kampinski Iger, 003
Hemmingway Greg, 014
Betz Stefan, 001
Bauer Alberta, 012
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 35
Page 69
Marianne WeiresC050
Lessing Katrin, 005
Schreiber Tobias, 007
Casper Martha, 006
Shakes William, 013
Naipaul Victor, 009
Humboldt Karla, 015
Bauer Jürgen, 010
Voltus Jean, 011
Schwartzkopf Walter, 008
Polak Edgar, 002
Mayer Karl, 004
Kampinski Iger, 003
Hemmingway Greg, 014
Betz Stefan, 001
Bauer Alberta, 012
3 Schritte zum Finden der internen Adresse und 1 Schritt zum Nachschauen des Datensatzes in der Tabelle.
Page 70
Marianne WeiresC050
Performanz und PROC SQL
• Manche Knoten sind durch das Umorganisieren schlechter als vorher platziert (z.B. „Betz Stefan“)
Im Durchschnitt verringert sich aber die Anzahl der Vergleiche
proc sort data = my_db.two force;by A;
run;
• zusätzlicher Speicherverbrauch für einen Indexjedoch werden die meisten Datenbanken viel häufiger gelesen als geschrieben
• Indexierte Daten müssen vor einer by Verarbeitung nicht mehr sortiert werden
• Blattknoten möglichst die gleiche Distanz zum Wurzelknotenbalancierter Baumnach Hinzufügen/Löschen Baum wieder ausbalancierenB-Bäume in DB (balancierte Bäume weniger Zugriffe)
• Indizes werden beim nachfolgenden sortieren ungültig (force Option setzen)
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 36
Page 71
Marianne WeiresC050
Performanz und PROC SQL
• Beispiel:proc sql;
select id, sex, couple_id from spouses.couples_02where id not in (select id from spouses.all);
quit;
NOTE: PROCEDURE SQL used (Total process time):real time 16:39.15cpu time 5:40.26
Ohne Index:
Mit Index (id): 476 create index id477 on spouses.couples_02(id);
real time 2:59.75cpu time 11.39 seconds
478479 create index id480 on spouses.all;
real time 5:18.38cpu time 10.93 seconds
NOTE: SQL Statement used (Total process time):real time 3:32.08cpu time 24.35 seconds
Page 72
Marianne WeiresC050
SAS und relationale Datenbanken
- SQL Erweiterungen der jeweiligen DB können genutzt werden
- Optimierung von joins und anderen Funktionen (Indizes der Datenbank genutzt)
• SAS/ACCESS Software:
Schnittstelle zwischen SAS SW und einem anderen DB Management System
Zugriffsmöglichkeiten:
libname Anweisung PROC SQL Pass-Through Facility
- 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 = testuserpassword = testpass database = mysqldbserver = mysqlserv port = 9876;
proc print data = my_lib.employees;
where dept = 'CSR010';run;
proc sql; connect to mysql (user = ...); select *
from connection to mysql(select * from customers);
disconnect from mysql; quit;
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 37
Page 73
Marianne WeiresC050
Zusammenfassung
• Nicht behandelt:- Aggregierungsfunktionen- Makrovariablen und SQL
• Fazit:
- Kompaktere Anweisungen mit PROC SQL
- Set Operatorenkein direktes Gegenstück für UNION, EXCEPT, INTERSECT in SAS
- Keine statistischen Auswertungen mit PROC SQL
- SQL ist DER Standard für relationale Datenbanksysteme
Page 74
Marianne WeiresC050
Literaturliste
Proceedings of the 12th Annual NorthEastSAS UsersGroup Conference, Washington, DC, 1999.
Undocumented and Hard-to-find SQL FeaturesKirk Paul Lafler (Paper 19-28)
Frequently Asked Questions about SAS Indexes (Paper 008-30)
An Introduction to SQL in SASPete Lund (Paper 257-30)
SQL SET OPERATORS: SO HANDY VENN YOU NEED THEM, Howard Schreier (PAPER 242-31)
Top Ten Reasons to Use PROC SQLWeiming Hu (Paper 042-29)
DATA Step vs. PROC SQL: What’s a neophyte to do?Craig Dickstein, (Paper 61-26)
An Introduction to PROC SQL, Rosalind K. Gusinow and J. Michael Miscisin (Hands-on Workshops)
SAS Institute Inc., 2004.SAS/ACCESS 9.1.2 Supplement for MySQL
SAS Institute Inc., 2004.SAS9.1 SQL Procedure User’s Guide.
PROC SQL for DATA Step Die-Hards, Williams, C.S. (manche Beispiele)
Creating and Exploiting SAS IndexesMichael A. Raithel (Paper 123-29)
*
*
**
*
Einführung in PROC SQL 10.07.2008
SAS-Treff am URZ Heidelberg 38
Page 75
Marianne WeiresC050
Einführung in PROC SQL
Vielen Dank für die Aufmerksamkeit!
Bei weiteren Fragen: m.weires@dkfz.de
Recommended