WS

Verarbeitung von Baumstrukturen beim Aggregieren

Homepage
Unternehmensprofil
Angebot
Vorträge
Gedanken
Tips
Kunden und Presse

Kontakt

 

Inhalt

Einführung
Beispiel für einen binären Baum
 
Programmierung
Beispiel für einen nicht-binären Baum
 
Programmierung
Ausgaben für einzelne Einheiten ohne Hierarchie
 
Programmierung
Optimierung
Fazit

Einführung

Hierarchien und andere Baumstrukturen kommen in Informationssystemen sehr häufig vor, und es gehört auch zum Allgemeinwissen, wie solche Strukturen in mehrdimensionalen Datenstrukturen (OLAP-Würfel) oder auch relationalen Strukturen verarbeitet werden: Wenn z.B. vorgerechnete Werte mit SAS erstellt werden sollen, verwendet man am einfachsten eine SAS-Tabelle, in der für jede Hierarchiestufe eine Variable mit den entsprechenden Ausprägungen steht, und erzeugt die Summen mit Proc Summary.

Etwas aufwendiger wird es, wenn feste Stufen fehlen, und/oder die Bäume nicht binär sind.

Für die folgenden Beispiele stellen wir uns eine große Makler-Organisation mit verschiedenen, hierarchisch strukturierten Geschäftsstellen vor. Gefragt wird nach der Anzahl der Immobilien und dem Gesamtwert im Einflussbereich einer Geschäftsstelle. Dabei verwenden wir drei Dateien:

  • Immobilien: Diese Datei enthält die Immobilien-Nummer (IMMO_NR) und den Wert (WERT)
  • Geschaeftsstellen: Diese enthält die Informationen zu den Geschäftsstellen(GESCHST_NR), unter anderem die direkt übergeordnete Organisationseinheit (OE_NR), welche wiederum eine Geschäftsstelle ist. Daraus muss dann die Hierarchie-Information aufgebaut werden.
  • Bestand: Diese Datei enthält die Zuordnung der Immobilien (IMMO_NR) zu der jeweiligen Geschäftsstelle (GESCHST_NR)

Beispiel für einen binären Baum

Für das erste Beispiel bleiben wir bei einem binären Baum und verwenden das folgende Organigramm:

Organigramm mit eindeutiger Zuordnung

Die Geschäftsstellen sind mit Nummern, die Immobilien mit Buchstaben gekennzeichnet. Die Geschäftsstelle 18 bietet die Immobilien A und B an. Insgesamt werden im Bereich der Geschäftsstelle 11 die Immobilien A bis F angeboten.

Die Organisation ist "etwas chaotisch", weil es keine festen Hierarchiestufen gibt oder diese in einigen Fällen übersprungen werden. Hinzu kommt, dass einige übergeordnete Geschäftsstellen selbst Immobilien anbieten. Eine solche Situation findet man z.B. oft bei so genannten "Struktur-Vertrieben". Aber wenigstens wird jede Immobilie nur von einer Geschäftsstelle angeboten. Auch ist jede Geschäftsstelle genau einer übergeordneten Geschäftsstelle zugeordnet.

Für den Benutzer sollen dann Tabellen in der folgenden Form aus vorgerechneten Werten erstellt werden:

Immobilien im Bereich der Geschäftsstelle Nr 12
Geschäftsstelle Anzahl Wert
16 1 100.000
17 2 180.000
eigenes Angebot 1 200.000
Insgesamt 4 480.000

Dazu wird folgende Ergebnisdatei benötigt (Ausschnitt):

OE_NR GESCHST_NR ANZAHL WERT
12 16 1 100.000
12 17 2 180.000
12 99900 (eigenes Angebot) 1 200.000
12 99999 (Insgesamt) 4 480.000

Programmierung

Das Programm zur Aufbereitung der Daten besteht dann aus drei Teilen:

  1. Aufbereiten der Hierarchie-Informationen aus der Datei "Geschaeftsstellen" und der Daten
  2. Berechnen der Werte für alle Geschäftsstellen
  3. Aufbereiten der Ergebnisdatei

Bei dem folgenden Lösungsvorschlag wird vorausgesetzt, dass die Datei "Geschaeftsstellen" einen Index auf das Feld GESCHST_NR besitzt und die Dateien Immobilien und Bestand nach der Immobiliennummer (IMMO_NR) sortiert sind. Hier wird zunächst ein Datastep-View erstellt, weil die Daten nur einmal von Proc Summary gelesen werden. Je nach Plattform kann es aber besser sein, eine physische Datei zu verwenden.

Data work.erg1V   / view = work.erg1V;
Length
immo_nr $1
wert 8
geschSt_nr 8
oe_nr 8
;
Keep
immo_nr
wert
geschSt_nr
oe_nr
;
/** Zusammenführen der Immobiliendaten und der Zuordnung zu den anbietenden Geschäftsstellen */
Merge work.Immobilien (keep=immo_nr wert)
work.Bestand (keep=immo_nr geschSt_nr);
By immo_nr;

geschSt_nr_alt = geschSt_nr;

/** Zunächst werden die Sätze für die übergeordneten
Geschäftsstellen erzeugt
*/
do while(_IORC_ EQ 0);
Set work.Geschaeftsstellen (keep = oe_nr geschSt_nr) key = geschSt_nr /UNIQUE;
if _iorc_ EQ 0 and oe_nr ne . then do;
Output;
geschSt_nr = oe_nr;
end;
else _iorc_ = 1;
end;
_iorc_ = 0;
_error_ = 0;

/** Jetzt kommt die Ausgabe für das "eigene Angebot" */
oe_nr = geschSt_nr_alt;
geschSt_nr = 99900;
Output;
Run;

Jetzt können die Summen für die Geschäftsstellen mit einem einfachen Proc Summary berechnet werden:

Proc summary data=work.erg1V;
Class oe_nr geschSt_Nr;
Types oe_nr oe_nr*geschSt_Nr;
Var wert;
Output out=work.erg2 sum=;
Run;

Anschließend wird noch die besondere "Geschäftsstellen-Nummer" für das eigene Angebot erzeugt und die Datei zur besseren Übersicht sortiert.

Data work.erg2;
Set work.erg2;
if geschSt_nr EQ . then geschSt_nr = 99999;
Rename _freq_ = anzahl;
Drop _type_;
Run;
Proc sort data=work.erg2;
By oe_nr geschSt_nr;
Run;

Beispiel für einen nicht-binären Baum

Gibt es dass wirklich?

Organigramm mit doppelter Immobilien-Zuordnung

Die Immobilien F und M werden gleichzeitig von 2 Geschäftsstellen angeboten. Solche Konstellationen sind selten, aber sie kommen vor und sind mit dem eben beschriebenen Ansatz leicht zu handhaben. Man muss nur darauf achten, dass die Summe für eine Geschäftsstelle nicht der Summe aus den untergeordneten Geschäftsstellen entspricht, weil es sonst zu Doppelzählungen kommen würde. Zum Beispiel müsste das Ergebnis für die Geschäftsstelle 12 jetzt wie folgt aussehen:

Immobilien im Bereich der Geschäftsstelle Nr 12
Geschäftsstelle Anzahl Wert
16 2 300.000
17 2 180.000
eigenes Angebot 1 200.000
Insgesamt 4 480.000

Hinweis: Eine Immobilie wird von zwei Geschäftsstellen angeboten.

Programmierung

Um Doppelzählungen zu vermeiden, müssen jetzt in den Datastep-View auch Sätze für die Gesamtsummen der Geschäftsstellen ausgegeben werden:
Data work.erg1V   / view = work.erg1V;
Length
immo_nr $1
wert 8
geschSt_nr 8
oe_nr 8
;
Keep
immo_nr
wert
geschSt_nr
oe_nr
;
/** Zusammenführen der Immobiliendaten und der Zuordnung zu den anbietenden Geschäftsstellen */
Merge work.Immobilien (keep=immo_nr wert)
work.Bestand (keep=immo_nr geschSt_nr);
By immo_nr;

geschSt_nr_alt = geschSt_nr;

/** Zunächst werden die Sätze für die übergeordneten
Geschäftsstellen erzeugt
*/
do while(_IORC_ EQ 0);
Set work.Geschaeftsstellen (keep = oe_nr geschSt_nr) key = geschSt_nr /UNIQUE;
if _iorc_ EQ 0 and oe_nr ne . then do;
Output;
/** Jetzt kommt der Satz für die Gesamtsumme **/
geschSt_nr = 99999;
Output; 
geschSt_nr = oe_nr;
end;
else _iorc_ = 1;
end;
_iorc_ = 0;
_error_ = 0;

/** Jetzt kommt die Ausgabe für das "eigene Angebot" */
oe_nr = geschSt_nr_alt;
geschSt_nr = 99900;
Output;
/** Auch hier benötigen wir einen Satz für die Gesamtsumme **/
geschSt_nr = 99999;
Output; 
Run;

Im nächsten Schritt werden Doubletten entfernt:

Proc sort data=work.erg1V out=work.erg1S nodupkey;
By oe_nr geschSt_Nr immo_nr;
Run;

Dann können die Summen für die Geschäftsstellen mit einem einfachen Proc Summary berechnet werden. Dabei reicht wegen der Sortierung sogar eine BY-Untergruppenverarbeitung:

Proc summary data=work.erg1S;
By oe_nr geschSt_Nr;
Var wert;
Output out=work.erg2
(Rename=(_freq_ = anzahl) Drop=_type_) sum=;
Run;

Ausgaben für einzelne Einheiten ohne Hierarchie

Natürlich werden auch Übersichten benötigt, in denen für eine Organisationseinheit die Werte nach anderen Merkmalen aufgeschlüsselt sind. Im folgenden Beispiel ist es die Immobilienart:

Immobilien im Bereich der Geschäftsstelle Nr 12
Immobilien-Art Anzahl Wert
Wohnung 1 100.000
Reihenhaus 2 420.000
Mehrfamilienhaus 1 700.000
Insgesamt 4 1.220.000

Dabei muss dann zwischen dem eigenen Angebot und dem Angebot aus dem Bereich insgesamt unterschieden werden. Da dies bei den meisten Geschäftsstellen dasselbe ist, spart man Platz, wenn man zwei Kennzeichen, z.B. "eigenes Angebot" und "Angebot insgesamt", einführt, wozu dann folgende Ergebnisdatei benötigt wird (Ausschnitt):

OE_NR eigenes_Angebot Angebot_insgesamt IMMO_ART ANZAHL WERT
12 0 1 W (Wohnung) 1 100.000
12 0 1 R (Reihenhaus) 2 420.000
12 0 1 M (Mehrfamilienhaus) 1 700.000
12 0 1 Z (Insgesamt) 4 1.220.000
12 1 0 M (Mehrfamilienhaus) 1 700.000
12 1 0 Z (Insgesamt) 1 700.000
24 1 1 M (Mehrfamilienhaus) 1 700.000
24 1 1 W (Wohnung) 1 100.000
24 1 1 Z (Insgesamt) 2 800.000

Programmierung

Würde man bei den Geschäftsstellen ohne unterstellte Geschäftsstellen auf die Zusammenfassung der Sätze für "eigenes Angebot" und  "Angebot insgesamt" verzichten, wäre es ausreichend, die künstlichen Geschäftsstellen-Nummern aus dem letzten Beispiel (99900 und 99999) zu verwenden. Wenn man den Plattenplatz sparen will, muss man zusätzlich prüfen, ob die jeweilige Geschäftsstelle untergeordnete Geschäftsstellen hat. Das Programm muss dann wie folgt modifiziert werden: 

Data work.erg1V / view = work.erg1V;
Length
oe_nr 8
eigenes_angebot $1
angebot_insgesamt $1
immo_nr $1

wert 8
immo_art $1
;
Keep
oe_nr
eigenes_angebot
angebot_insgesamt

immo_nr
wert
immo_art
;

/** Zusammenführen der Immobiliendaten und der Zuordnung zu den anbietenden Geschäftsstellen */
Merge work.Immobilien (keep=immo_nr wert immo_art)
work.Bestand (keep=immo_nr geschSt_nr);
By immo_nr;

geschSt_nr_alt = geschSt_nr;

/** Zunächst werden die Sätze für die übergeordneten
Geschäftsstellen erzeugt
*/
do while(_IORC_ EQ 0);
Set work.Geschaeftsstellen (keep = oe_nr geschSt_nr) key = geschSt_nr /UNIQUE;
if _iorc_ EQ 0 and oe_nr ne . then do;
/** Ausgabe für die Gesamtsumme bei überstellter OE-Einheit **/
angebot_insgesamt = 1;
eigenes_angebot = 0;
Output; 
geschSt_nr = oe_nr;
end;
else _iorc_ = 1;
end;

/** Jetzt kommt die Ausgabe für das "eigene Angebot" */
oe_nr = geschSt_nr_alt;
angebot_insgesamt = 1;
/** Prüfung, ob die Geschäftsstelle als OE-Einheit unterstellte Geschäftsstellen hat */
Set work.Geschaeftsstellen (keep = oe_nr) key = oe_nr /UNIQUE;
if _iorc_ EQ 0 then do;
/** Es gibt unterstellte Geschäftsstellen, dann getrennte Ausgabe */
eigenes_angebot = 0;
Output;
angebot_insgesamt = 0;
end;
eigenes_angebot = 1;
Output;
_iorc_ = 0;
_error_ = 0;
Run;

Im nächsten Schritt werden wieder Doubletten entfernt, und anschließend wird summiert:

Proc sort data=work.erg1V out=work.erg1S nodupkey;
 By oe_nr eigenes_angebot angebot_insgesamt immo_nr;
Run;
Proc summary data=work.erg1S;
 By oe_nr
eigenes_angebot angebot_insgesamt;
Class immo_art;
 Var wert;
 Output out=work.erg2 sum=;
Run;
Die Gesamtsummen stehen dann unter _TYPE_=0, die Summen für die Immobilien-Arten stehen unter _TYPE_=1.
Data work.erg2;
Set work.erg2;
if _type_ EQ 0 then immo_art = "Z";
Rename _freq_ = anzahl;
Drop _type_;
Run;
Proc sort data=work.erg2;
By oe_nr eigenes_angebot immo_art;
Run;

Optimierung

In dem Datastep-View erfolgen viele indizierte Zugriffe auf die Geschäftsstellen-Tabelle, welche in diesem Umfang nicht unbedingt erforderlich sind. Der Vorteil dieses Algorithmus besteht vor allem darin, dass die maximale Zahl der Überstellungen nicht angegeben werden muss, und dass so auf Macrovariablen und Macros zur Anpassung verzichtet werden kann.

Aber man kann auch zuerst die Hierarchie so auflösen, dass die erforderlichen Informationen in einem Datensatz stehen und zunächst mit der Bestands-Zuordnung verknüpft werden. Im folgenden Beispiel wird zur besseren Verständlichkeit SQL verwendet. Eine Datastep-Lösung lässt sich sehr leicht aus den oben stehenden Datastep-Views ableiten, würde aber bei der unten eingeführten Erweiterung, dass Geschäftsstellen an mehrere OE-Einheiten berichten können, sehr kompliziert.

%Macro HierarchieAufloesen(maxStufe);
%Local i ;
Proc sql;
create table work.Hierarchie as
select
g1.geschSt_nr
%do i = 1 %to &maxStufe;
,g&i..oe_nr as oe_nr&i
%end;
from work.Geschaeftsstellen g1
%do i = 2 %to &maxStufe;
left join work.Geschaeftsstellen g&i on (g%eval(&i-1).oe_nr EQ g&i..geschSt_nr)
%end;
order by geschSt_nr
;
quit;
%Mend;
%hierarchieAufloesen(4);

Die Ergebnisdatei hat dann folgenden Aufbau:

GeschSt_Nr  oe_nr1    oe_nr2    oe_nr3    oe_nr4

10 . . . .
11 10 . . .
12 10 . . .
13 11 10 . .
14 11 10 . .
15 10 . . .
16 12 10 . .
17 12 10 . .
18 13 11 10 .
19 13 11 10 .
20 14 11 10 .
21 15 10 . .
22 15 10 . .
23 10 . . .
24 16 12 10 .

Zu jeder Geschäftsstelle werden, sofern vorhanden, alle übergeordneten OE-Einheiten geliefert.

Wer es sich jetzt ganz bequem machen will, fügt auch noch gleich die Zuordnung der Immobilien aus der Datei Bestand hinzu und kontrolliert, ob die Geschäftsstelle untergeordnete Geschäftsstellen hat: 

%Macro StrukturErstellen(maxStufe);
%Local i ;
Proc sql;
create table work.Struktur as
select
i.immo_nr
,g1.geschSt_nr
,case(hu.oe_nr)
when(.) then 0
else 1
end as hatUntergeordnete

%do i = 1 %to &maxStufe;
,g&i..oe_nr as oe_nr&i
%end;
from (
work.Geschaeftsstellen g1
left join (select distinct oe_nr from work.Geschaeftsstellen) hu
on (hu.oe_nr EQ g1.geschSt_nr)

%do i = 2 %to &maxStufe;
left join work.Geschaeftsstellen g&i on (g%eval(&i-1).oe_nr EQ g&i..geschSt_nr)
%end;
),
work.Bestand i
where i.geschSt_nr EQ g1.geschSt_nr
order by immo_nr

;
quit;
%Mend;
%StrukturErstellen(4);

Dann lassen sich sogar die Fälle mühelos verarbeiten, in denen eine OE-Einheit gleichzeitig an zwei übergeordnete OE-Einheiten berichtet, wie in dem folgenden Organigramm, bei dem die Geschäftsstelle 23 gleichzeitig an die OE-Einheiten 10 und 16 berichtet:

Organigramm, bei dem die OE 23 gleichzeitig an 16 und 10 berichtet

Die Ergebnisdatei hat dann folgenden Aufbau:

Immo_Nr  GeschSt_Nr  Untergeordnete    oe_nr1    oe_nr2    oe_nr3    oe_nr4

A 18 0 13 11 10 .
B 18 0 13 11 10 .
C 19 0 13 11 10 .
D 14 1 11 10 . .
E 20 0 14 11 10 .
F 21 0 15 10 . .
F 20 0 14 11 10 .
G 21 0 15 10 . .
H 22 0 15 10 . .
I 22 0 15 10 . .
J 23 0 16 12 10 .
J 23 0 10 . . .
K 23 0 16 12 10 .
K 23 0 10 . . .
L 24 0 16 12 10 .
M 12 1 10 . . .
M 24 0 16 12 10 .
N 17 0 12 10 . .
O 17 0 12 10 . .

In der weiteren Verarbeitung wird diese Tabelle dann über ein Merge mit den Immobiliendaten verknüpft. Die Zugriffe auf die Geschäftsstellen-Tabelle werden durch die Verarbeitung eines Arrays ersetzt. Hier am Beispiel der Aufbereitung für die Immobilienarten:

Data work.erg1V / view = work.erg1V;
Length
oe_nr 8
eigenes_angebot $1
angebot_insgesamt $1
immo_nr $1
wert 8
immo_art $1
;
Keep
oe_nr
eigenes_angebot
angebot_insgesamt
immo_nr
wert
immo_art
;
/** Zusammenführen der Immobiliendaten und der Zuordnung zu den anbietenden Geschäftsstellen */
Merge work.Immobilien (keep=immo_nr wert immo_art)
work.Struktur ;
By immo_nr;

Array oe_nrA (_I_) oe_nr1 - oe_nr4;

/** Zunächst werden die Sätze für die übergeordneten
Geschäftsstellen erzeugt
*/
do over oe_nrA;
if oe_nrA NE . then do;
/** Ausgabe für die Gesamtsumme bei überstellter OE-Einheit **/
oe_nr = oe_nrA;

angebot_insgesamt = 1;
eigenes_angebot = 0;
Output;
end;
else _I_ = 999; * do over-Schleife beenden;
end;

/** Jetzt kommt die Ausgabe für das "eigene Angebot" */
oe_nr = geschSt_nr;
angebot_insgesamt = 1;
/** Prüfung, ob die Geschäftsstelle als OE-Einheit unterstellte Geschäftsstellen hat */
if hatUntergeordnete then do;
/** Es gibt unterstellte Geschäftsstellen, dann getrennte Ausgabe */
eigenes_angebot = 0;
Output;
angebot_insgesamt = 0;
end;
eigenes_angebot = 1;
Output;
Run;

und weiter, wie gehabt:

Proc sort data=work.erg1V out=work.erg1S nodupkey;
By oe_nr eigenes_angebot angebot_insgesamt immo_nr;
Run;

Proc summary data=work.erg1S;
By oe_nr eigenes_angebot angebot_insgesamt;
Class immo_art;
Var wert;
Output out=work.erg2 sum=;
Run;


Data work.erg2;
Set work.erg2;
if _type_ EQ 0 then immo_art = "Z";
Rename _freq_ = anzahl;
Drop _type_;
Run;
Proc sort data=work.erg2;
By oe_nr eigenes_angebot immo_art;
Run;

Fazit

Diese Beispiele zeigen, dass sich mit SAS auch komplexe Situationen relativ leicht handhaben lassen, wenn man nicht dogmatisch darauf besteht, einen mehrdimensionalen Würfel (OLAP) zu erzeugen, für den feste Hierarchiestufen benötigt würden.


 
 

 
 
Ihr Kommentar
(alle Angaben sind freiwillig)
Ihr Name :
Ihre Emailadresse:
Telefon-Nummer:
Ihre Meinung:
Bitte nehmen Sie Kontakt zu mir auf:
per Email   rufen Sie mich an  

©WS Unternehmensberatung und Controlling-Systeme GmbH
Friedrich-Weinbrenner-Straße 20
69126 Heidelberg

Tel.: 06221 / 401 409
Fax: 06221 / 401 422

EMail: info @ ws-unternehmensberatung.de

Amtsgericht Mannheim, HRB 335485
Geschäftsführer: Wilfried Schollenberger
zurück zum Anfang