Excel Steuerung mit SAS

Aus BI-Snippets - Business Intelligence Code und Module
Wechseln zu: Navigation, Suche

"Von hinten durch die Brust ins Auge" Excel Dateien mit SAS einmal anders erstellen

Wohl bekannt sind die Möglichkeiten, Excel (fähige) Dateien via proc export, proc report und dem ODS zu erstellen. Interessant ist aber auch die Möglichkeit, aus Excel heraus mit der Dateigenerierung zu starten und dabei den vollen Funktionsumfang von Excel-VBA für Formatierungen und Co. nutzen zu können.

Möglich machen dies die SAS Integration Technologies. SAS stellt hier ActiveX Bibliotheken zur Verfügung, die in Visual Basic (VB) eingebunden werden können. Mit Hilfe dieser ActiveX Bibliotheken kann man aus VB bzw. VBA mit dem SAS Integrated Object Model interagieren. Es werden für das Beispiel mindestens drei ActiveX-Bibliotheken benötigt:

a) "Integrated Object Model (IOM)"" (sas.tlb)
b) "SASObjectManager" (SASOMan.dll) und
c) "SASWorkspaceManager" (SASWMan.dll)

Alle Dateien finden sich unter dem Installationspfad von SAS, ...\SharedFiles\Integration Technologies\.

Mögliches Szenario: In einem Excel Tabellenblatt sollen die Daten aus über 15 unterschiedlichen SAS-Datasets in separaten Tabellen angezeigt werden. Dabei ist im Fuß jeder Tabelle die einzelnen Spaltensummen enthalten. Diese sind "Fett" zu formatieren und der Hintergrund der Fußzeile ist einzufärben. Bei bestimmten Tabellen umfasst der Tabellenfuß vier Zeilen, die die Extremwerte einer bestimmten Spalte enthalten. Die Extremwerte sind in einer anderen Farbe als die anderen Werte darzustellen. Um jede Tabelle ist ein Rahmen zu ziehen.

Vorgehensweise: Der erste Schritt ist die Erzeugung einer lokalen SAS-Session. Dazu wird der SASWorkspaceManager benutzt. Zunächst werden die benötigten Objekte definiert.

   Dim sasWSM As New SASWorkspaceManager.WorkspaceManager
   Dim sasWS As SAS.Workspace

Das Schlüsselwort New erzeugt hier ein neues WorkspaceManager-Objekt. Ein Workspace entspricht einer SAS-Session. Über die Methode "CreateWorkspaceByServer" wird letztlich die SAS-Session erzeugt.

   'SAS initialisieren
   Set sasWS = sasWSM.Workspaces.CreateWorkspaceByServer("Local", VisibilityProcess, Nothing, "", "", v_sas_error)

Mit dem nun erzeugten Workspace-Objekt ist man nun in der Lage, mit Hilfe der Methode "Submit" SAS Code abzusetzen. Dies wird benutzt, um einen Libref auf die benötigten SAS-Datasets zu erzeugen:

 'Libname anlegen
 sasWS.LanguageService.Submit "libname myFoo '" & v_lib_path & "';"
 

Über den vorhandenen Libref können jetzt die Datasets bequem eingelesen werden. Dazu wird ein ADODB Connection Objekt erzeugt, welches über den SASWorkspaceManager an die bestehende SAS-Session gebunden wird:

 con.Open "provider=sas.iomprovider.1;SAS Workspace ID=" & sasWS.UniqueIdentifier
 

Über eine Schleifenverarbeitung wird jedes Dataset eingelesen und innerhalb der Schleife die benötigten Formatierungen vorgenommen. Die Namen der zu lesenden Datasets werden hier über die Variable v_datasets bestimmt.

   For Each v_ds In v_datasets
   
     rs.Open "myFoo." & v_ds, con, adOpenDynamic, adLockReadOnly
     ...
     
   Next v_ds
   

Da ein Dataset als ADODB-Recordset vorliegt, können die Standard-Variablen des Recordset-Objekts für eine dynamische Bestimmungen von Spalten und Zeilen genutzt werden:

   'Erst Spaltenbeschriftungen
       For Each fld In rs.Fields
           .Cells(v_row_pos, v_col_pos).Value = fld.Name
           .Cells(v_row_pos, v_col_pos).Font.Bold = True
           .Cells(v_row_pos, v_col_pos).HorizontalAlignment = xlCenter
           'Tabelleende gleich mit formatieren
           .Cells(v_row_pos + rs.RecordCount, v_col_pos).Font.Bold = True
           v_col_pos = v_col_pos + 1
           v_fld_cnt = v_fld_cn

     Next
   

Das Einfügen der Daten aus dem Recordset erfolgt mittels der Methode CopyFromRecordset, die Excel-VBA anbietet. Damit können auf einfach Weise die Daten aus dem Recordset nach Excel übertragen werden.

   .Cells(v_row_pos, v_col_pos).CopyFromRecordset rs
   

Anschließend werden wieder die Variablen des Recordsets benutzt, um den Rahmen dynamisch setzen zu können.

   .Range(Cells(v_row_pos - 1, 1), Cells(v_row_pos + rs.RecordCount, v_col_pos + rs.Fields.Count - 1)).Select
   Selection.Borders(xlDiagonalDown).LineStyle = xlNone
       Selection.Borders(xlDiagonalUp).LineStyle = xlNone
       With Selection.Borders(xlEdgeLeft)
           .LineStyle = xlContinuous
           .ColorIndex = 0
          
           .Weight = xlMedium
       End With
       With Selection.Borders(xlEdgeTop)
           .LineStyle = xlContinuous
           .ColorIndex = 0
          
           .Weight = xlMedium
       End With
       With Selection.Borders(xlEdgeBottom)
           .LineStyle = xlContinuous
           .ColorIndex = 0
           
           .Weight = xlMedium
       End With
       With Selection.Borders(xlEdgeRight)
           .LineStyle = xlContinuous
           .ColorIndex = 0
           
           .Weight = xlMedium
       End With
       On Error Resume Next
       Selection.Borders(xlInsideVertical).LineStyle = xlHairline
       Selection.Borders(xlInsideHorizontal).LineStyle = xlHairline


Das Formatieren des Tabellenbodens erfolgt nach dem gleichen Schema. Nach Durchlauf des Codes liegt die gewünschte Excel-Datei vollständig formatiert vor. Manuelle Anpassungen sind nicht notwendig.

Sollen Daten aus unterschiedlichen SAS-Datasets in einem Tabellenblatt wiederkehrend angezeigt werden, stellen die SAS Integration Technologies eine interessante Alternative zu proc export, proc report und Co. dar. Gerade durch den vollen Zugriff auf Excel-VBA kann man sich von bei wiederkehrender Verarbeitung von zeitraubenden Formatierungsaufgaben befreien. Werden Konfigurationsmöglichkeiten wie Abstand der Tabellen, Spaltennamen, Datasetnamen, Farben etc. eingeplant, kann ein Anwender ohne VBA-Kenntnisse dennoch großen Einfluss auf das Erscheinungsbild der Excel-Datei nehmen. Ein Wartungsaufwand kann damit minimiert werden.