Expertentipp:
Wussten Sie schon, wie Sie im DATA STEP auf Werte in der vorigen Zeile oder der nachfolgenden Zeile zugreifen können?
Und so geht’s:
Der DATA STEP arbeitet zeilenweise, d.h. es wird immer genau eine Zeile eingelesen und verarbeitet. Manchmal muss man aber die eingelesenen Werte mit den vorigen oder nachfolgenden Werten vergleichen oder mit ihnen rechnen. Hier erfahren Sie, wie Ihnen das gelingt:
Im DATA STEP ist Zurückschauen einfacher, als Vorausschauen. Im SQL ist beides gleich aufwendig. Zuerst erstellen wir uns eine Testdatei:
data have;
input ID $ Index Measure;
cards;
A 1 11
A 2 12
A 3 13
B 1 21
B 2 22
;
run;
Erstellte Testdatei:
Jetzt wollen wir jede Zeile um den Wert von MEASURE aus der vorigen Zeile der jeweiligen ID und aus der nachfolgenden Zeile der jeweiligen ID erweitern. Falls es keine vorige Zeile bzw. nachfolgende Zeile zu der ID gibt, soll jeweils der Wert auf fehlend gesetzt werden:
Folgendes soll dabei herauskommen:
Und hier ist die Lösung im DATA STEP:
/* Lösung im Data Step */
data look_both_ways;
/* 1. SET: Satzweises Lesen der Datei HAVE */
set have;
by ID;
/* 2. SET: Gleichzeitiges Lesen der nächsten Zeile */
set have ( firstobs = 2 keep = Measure
rename = (Measure = Next_Measure) )
have ( obs = 1 drop = _all_);
Prev_Measure = ifn( first.ID, (.), lag(Measure) );
Next_Measure = ifn( last.ID, (.), Next_Measure );
run;
Die vorherigen Werte werden mit der LAG-Funktion ermittelt und die nachfolgenden Werte erfordern eine zusätzliche SET-Anweisung, die gut mit der ersten SET-Anweisung abgestimmt werden muss, um gleichzeitig noch die Variable MEASURE aus der nächsten Zeile einzulesen und dabei in Next_Measure umzubenennen.
Damit auch die allerletzte Zeile aus der Datei HAVE im 1.SET noch gelesen wird, wenn die Datei im 2. Set ja schon zu Ende ist, wird noch ein Dummy-HAVE im zweiten SET eingefügt. Sonst würde der Lesevorgang beendet werden, wenn die letzte Zeile der Datei HAVE im zweiten Set gelesen worden ist.
Dieser Lösungsansatz ist schnell und übersichtlich, wenn man den Trick mit der zweiten SET-Anweisung verstanden hat.
Im Folgenden finden Sie noch einen Lösungsansatz auf Basis von PROC SQL mit mehreren LEFT-Joins der Datei mit sich selbst:
proc sql;
create table look_both_ways as
select have.*
, next.Measure as Next_Measure
, prev.Measure as Prev_Measure
from have
left join
have as prev
on have.ID = prev.ID
and
have.Index = prev.Index + 1
left join
have as next
on have.ID = next.ID
and
have.Index = next.Index - 1
;
quit;
Diese Lösung benötigt die INDEX-Werte. Sie basiert nicht auf der Reihenfolge der Zeilen in der Datei.
Hier noch eine weitere Lösung mit PROC SQL ohne Verwendung der INDEX-Spalte, sondern durch Nutzen der Reihenfolge der Zeilen unter Verwendung von verschachtelten Queries:
proc sql;
create table look_both_ways as
select lookback.*, next.Measure as Next_Measure
from ( select have.*, prev.Measure as
Prev_Measure
from have left join have as prev
on have.ID = prev.ID and
prev.Measure < have.Measure
group by have.ID, have.Measure
having prev.Measure = max(prev.Measure)
) as lookback
left join have as next
on lookback.ID = next.ID and
next.Measure > lookback.Measure
group by lookback.ID, lookback.Measure
having next.Measure =
min(next.Measure)
;
quit;
Das ist ganz schön schwere Kost und schon sehr fortgeschrittenes SQL. Vielleicht ist es einfacher zu verstehen, wenn man es in zwei Schritte aufteilt und im ersten Schritt einen View erstellt, auf den man im zweiten Schritt zurückgreift.
/* Aufgesplittet in 2 Schritte:*/
proc sql;
create view lookback as
select have.*, prev.Measure as Prev_Measure
from have left join have as prev
on have.ID = prev.ID and
prev.Measure < have.Measure
group by have.ID, have.Measure
having prev.Measure = max(prev.Measure)
;
create table look_both_ways as
select lookback.*, next.Measure as Next_Measure
from lookback left join have as next
on lookback.ID = next.ID and
next.Measure > lookback.Measure
group by lookback.ID, lookback.Measure
having next.Measure = min(next.Measure)
;
quit;
In diesem Fall erzeugen alle vier Lösungen dieselben Ergebnisse. Allerdings ist die Verarbeitungsweise sehr unterschiedlich, so dass man diese vier Lösungsansätze nicht grundsätzlich gleichstellen kann.
Empfehlenswert ist der Ansatz mit dem DATA STEP, da mehrere Joins mit sich selbst bzw. verschachtelte Joins die Sache nicht wirklich schneller machen.
Diese und weitere Tipps erhalten Sie in den Kursen SAS® Programmierung 2: Datenmanagement im Data Step und SAS® SQL 1: Grundlagen.
