Einführung in PROC SQL - Universitaetsrechenzentrum · Einführung in PROC SQL 10.07.2008 SAS-Treff am URZ Heidelberg 3 Page 5 Marianne Weires C050 PROC SQL und SAS DATA Step •

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)



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 */



Page 5

Marianne WeiresC050


• 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


• 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



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



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;

*



Page 11

Marianne WeiresC050


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


- 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)



Page 13

Marianne WeiresC050


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


• 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



Page 15

Marianne WeiresC050


• 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




Page 17

Marianne WeiresC050


• 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


• 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;



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


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:

*



Page 21

Marianne WeiresC050


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


- 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:

*



Page 23

Marianne WeiresC050


- 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


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



Page 25

Marianne WeiresC050


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


proc sql;select *from my_db.bpwhere blutdruck = 'hoch';

quit;

- Auswahl von Beobachtungen (WHERE):

- einige Operatoren:



Page 27

Marianne WeiresC050


- 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


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;



Page 29

Marianne WeiresC050


• 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


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:



Page 31

Marianne WeiresC050


• Ä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


• 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



Page 33

Marianne WeiresC050


• 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


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



Page 35

Marianne WeiresC050


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


(Fast) immer will man nur eine Untermenge des kartesischen Produktes

Inner Join Right Join Left Join Full Join

Outer Joins



Page 37

Marianne WeiresC050


• 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


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;



Page 39

Marianne WeiresC050


• 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


• 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

*



Page 41

Marianne WeiresC050


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


• 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)

*



Page 43

Marianne WeiresC050


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


• 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

*



Page 45

Marianne WeiresC050


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


• 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“

*



Page 47

Marianne WeiresC050


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


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



Page 49

Marianne WeiresC050


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





inner join



Erste Abfrage liefert nur Einträge für Patienten, die eingeliefert worden sind und der Einweisungsarzt der Hausarzt des Patienten ist



Page 51

Marianne WeiresC050





inner join



Zweite Abfrage liefert nur unterschiedliche Einträge für jeden Arzt, der Einlieferungsarzt ist und gleichzeitig Hausarzt

Page 52

Marianne WeiresC050


Colanton4003Humboldt25MAY199715

Colanton4003Humboldt17AUG199715

MacArthu2322Bauer30NOV199810

Hanratty3274Schreibe28JUL19967

Hanratty3274Schreibe08SEP19967

Fitzhugh1972Lessing19JAN19995

Fitzhugh1972Lessing10MAR19995

Fitzhugh1972Lessing10APR19995

Fitzhugh1972Betz12APR19991

doclastnameMD_IDpatnameADMDATEPT_ID



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


• 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



Page 55

Marianne WeiresC050


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


• 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*



Page 57

Marianne WeiresC050


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


• 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



Page 59

Marianne WeiresC050


• 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


• Outer Union

proc sql;select * from my_db.oneouter unionselect* from my_db.two;

quit;

87..

65..

43..

42..

21..

..29

..32

..21

BAAY



Page 61

Marianne WeiresC050


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



Page 63

Marianne WeiresC050


• 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


Beispiel: Indexstruktur - binärer Baum

- zyklenfreie Graphstruktur - besteht aus Knoten verbunden

durch gerichtete Kanten

KarlaHumboldt015GregHemmingway014

WilliamShakes013AlbertaBauer012JeanVoltus011

JürgenBauer010VictorNaipaul009WalterSchwartkopf008TobiasSchreiber007MarthaCasper006KatrinLessing005KarlMayer004IgorKampinski003

EdgarPolak002StefanBetz001

firstnamelastnamerecord identifier



Page 65

Marianne WeiresC050


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



Page 67

Marianne WeiresC050

Lessing Katrin, 005


Casper Martha, 006

Shakes William, 013

Naipaul Victor, 009

Humboldt Karla, 015

Bauer Jürgen, 010

Voltus Jean, 011


Polak Edgar, 002

Mayer Karl, 004

Kampinski Iger, 003


Betz Stefan, 001

Bauer Alberta, 012

Page 68

Marianne WeiresC050

Lessing Katrin, 005


Casper Martha, 006

Shakes William, 013

Naipaul Victor, 009

Humboldt Karla, 015

Bauer Jürgen, 010

Voltus Jean, 011


Polak Edgar, 002

Mayer Karl, 004

Kampinski Iger, 003


Betz Stefan, 001

Bauer Alberta, 012



Page 69

Marianne WeiresC050

Lessing Katrin, 005


Casper Martha, 006

Shakes William, 013

Naipaul Victor, 009

Humboldt Karla, 015

Bauer Jürgen, 010

Voltus Jean, 011


Polak Edgar, 002

Mayer Karl, 004

Kampinski Iger, 003


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


• 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)



Page 71

Marianne WeiresC050


• 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;



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)

*

*

**

*



Page 75

Marianne WeiresC050

Einführung in PROC SQL

Vielen Dank für die Aufmerksamkeit!

Bei weiteren Fragen: [email protected]

Documents

Einführung in PROC SQL - Universitaetsrechenzentrum · Einführung in PROC SQL 10.07.2008 SAS-Treff am URZ Heidelberg 3 Page 5 Marianne Weires C050 PROC SQL und SAS DATA Step •