Verarbeitung von Baumstrukturen beim Aggregieren

Inhalt

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:

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:

Dazu wird folgende Ergebnisdatei benötigt (Ausschnitt):

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?

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:

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:

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

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:

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.