Datenexport mittels SAS Stored Process - mayato€¦ · mayato GmbH – – [email protected] White...

White Paper

Datenexport mittels SAS Stored Process // Benno Schultheiss

mayato GmbH – www.mayato.com – [email protected]

White Paper // Datenexport mittels SAS Stored Process

Zusammenfassung

In diesem White Paper wird ein SAS® Stored Process zur Selektion und Speicherung von Daten mit grafischer Oberfläche vorgestellt. Die Anwendung bietet eine direkte Selektion und Extraktion von Daten nach diversen Parametern, wie gewünschten Variablenwerten, Zeitraum und Spalten. Dadurch wird Analysten eine einfache Möglichkeit gegeben, relevante Daten zu extrahieren und außerhalb von SAS® zu verarbeiten.

Der in diesem Paper demonstrierte Prozess erweitert diese Funktionalitäten um eine Überprüfung der Datenmenge, eine Dokumentation der Abfrageparameter im HTML-Format und eine Ausgabe der Ergebnisse als ZIP-Datei. Der Prozess lässt sich leicht auf neue Daten übertragen und um unterschiedliche Parameter erweitern. Das White Paper gibt zunächst eine Übersicht über die Funktionsweise des SAS® Stored Process und das Anwendungsbeispiel. Im weiteren Verlauf wird der Code detaillierter erläutert.

Schlüsselwörter

SAS® Stored Process Macros Parameter HTML Data Export CSV ZIP

Zielgruppe

Dieses Paper richtet sich an SAS® Entwickler, die eine einfache und effiziente Lösung suchen, um Nutzern die Datenextraktionen ohne SAS® oder Datenbankvorkenntnisse zu ermöglichen.

Grundlegende Informationen

Ein SAS® Stored Process ist ein SAS® Programm, das auf einem Server gehostet und von Metadaten beschrieben wird. Als Hosts stehen SAS® Stored Process Server und SAS® Workspace Server zur Verfügung. Durch Input-Parameter ist es möglich, einen dynamischen Stored Process zu erzeugen. Mit Output-Optionen kann zudem die Ausgabe der Prozessergebnisse gesteuert werden.

Ein Stored Process wird in drei grundlegenden Schritten erstellt:

Programmieren des Stored Process Auswahl des Servers, auf dem der Stored Process ausgeführt werden soll Registrieren der Metadaten des Stored Process

Der Stored Process wird beispielsweise für Web Reporting, analytische Anwendungen, Übertragung von Daten an Clients oder dem Exportieren von Daten verwendet. Dabei können alle SAS® Datenquellen und externe Daten angesteuert und neue Datensätze bzw. Datenziele erzeugt werden.



Was wird für das demonstrierte Beispiel benötigt?

Die Demonstration des Prozesses in diesem White Paper greift auf die SAShelp Tabelle „MDV“ zurück.

Server Anforderungen:

SAS® Stored Process Web Application (Integration Technologies)

Client Anforderungen:

Web Browser Web Application Framework oder Library. Bei aktuellen Browsern ist dies stets vorhanden.

Funktion des SAS® Stored Process

Zum Exportieren der Ergebnisse von parametrisierten Abfragen wird ein Stored Process um drei Funktionen erweitert:

Der Prozess prüft die Anzahl der Zeilen der Abfrage und gibt bei einer zu großen Menge eine Nachricht an den Nutzer zurück. Im Anschluss kann der Nutzer eine limitierte Anzahl an Zeilen exportieren.

Es wird eine HTML-Datei erstellt, in der die Abfrageparameter dokumentiert werden. Die Ergebnisse werden zusammen mit der HTML-Datei als ZIP-Datei gespeichert.

Die Dokumentation der Abfrageparameter hilft dem Nutzer, vergangene Abfragen nachvollziehen zu können.

Grundstruktur des SAS® Codes

Die Funktionsweise des SAS® Stored Process besteht aus drei Komponenten:

1. Input: Der Stored Process nutzt Eingabe-Parameter, um die nötigen Daten abzufragen. Diese werden durch den Nutzer im Eingabefenster eingestellt. Die Abfrage wird auf eine in diesem Beispiel vordefinierte Tabelle angewendet.

2. Processing: Im Stored Process werden die Input Daten und Parameter zu Abfragen zusammengestellt. Die Prozesslogik prüft, ob Daten vorhanden sind und setzt ein Limit für die maximale Datenmenge. Überschreitet die Datenmenge das Limit nicht, wird die Abfrage durchgeführt und eine CSV-Datei zusammen mit einem HTML-Report zu einer ZIP-Datei zusammengefasst und heruntergeladen. Produziert die Abfrage zu viele Zeilen, wird dies in einer HTML-Nachricht mit der Option angezeigt, die maximal mögliche Anzahl an Zeilen herunter zu laden. Ergibt die Abfrage keine Daten, wird dies dem Nutzer ebenfalls in einer HTML-basierten Nachricht mitgeteilt.

3. Output: Die Ausgabekomponenten des Stored Process umfassen das ZIP-File mit CSV-Daten und HTML-Reports oder die HTML-basierten Nachrichten an den Nutzer.

Die folgende Grafik gibt die oben beschriebenen Komponenten im schematischen Zusammenhang wieder:



OutputSTP ProcessingINPUT

Stored Process• Prüft Datenverfügbarkeit.• Prüft, ob Datenmenge im Limit.• Erstellt Abfrage für Anzeige der

verfügbaren Daten in Eingabe-Fenster.

• Erstellt Abfrage nach ausgewählten Parametern und generiert HTML-Report.

• Erstellt ZIP-Datei und speichert oder meldet zu große Datenmenge.

DATA_TABLE

EXP_TABLEEXP_TABLE

EXP_TABLE

Input Parameter

Work.data + HTML Report

ZIP-File:CSV-Data+

HTML-Report

HTMLOutput

Message

numRows < Limit

numRows > Limit

Abfrage ZIP-File

Daten-selektion

Option: Extraktion von maximaler Anzahl Zeilen

Prozess-Diagramm SAS Stored Process

HTMLOutput

MessagenumRows = 0

Abbildung 1: Prozess-Diagramm SAS® Stored Process

Tabelle 1 listet die Eingabeparameter des Stored Process auf. Anhand dieser Parameter werden die Daten selektiert.

Tabelle 1: Stored Process Input Prompts

ParametersDisplayed Text Name TypeCode: pCode Text

OrigCity: pOrigCity Text

Time Interval: pInterval Date range

Table Columns: pTableCols Data source item

Stored Process Input Prompts

Im hier demonstrierten Beispiel werden Verkäufe nach Art der Sendung durch einen Code unterschieden. Der zweite Parameter lässt den Nutzer die Stadt einstellen, aus der eine Sendung versandt wurde. Neben dem Intervall für den relevanten Zeitraum können die Spalten für die Export-Tabelle ausgewählt werden.

Um die Selektion korrekt vornehmen zu können, werden zwischen den Parametern Abhängigkeiten einprogrammiert. Diese Abhängigkeiten legen fest, in welcher Reihenfolge die Parameter eingestellt werden müssen. Bei dem „Code“ und „OrigCity“ Parameter werden die zur Auswahl stehenden einzigartigen (unique) Werte des Parameters angezeigt, um diese für die Abfrage selektieren zu können.

Abbildung 2 zeigt die Struktur der Abhängigkeiten und welches Format die einzelnen Parameter annehmen. Die Abhängigkeiten sind durch die grünen Pfeile dargestellt und zeigen durch die



Pfeilrichtung die Abhängigkeit eines Parameters von den Angaben des vorherigen an. Die Description zeigt einen nicht veränderbaren Parameter an, der zur Information des Nutzers genutzt werden kann. In diesem Beispiel wird die Description für eine knappe Erläuterung des Stored Process verwendet.

Abhängigkeiten – Reihenfolge der DateneingabeEingabemaske für Datenexport

OrigCity

Code

TABLE_COLUMNS

TIME_INTERVAL

Description

Text

Text

Date range

Data source item

Pfeil zeigt Abhängigkeit an.

Abbildung 2: Abhängigkeiten der Parameter in der Eingabemaske

Die in der Processing-Komponente beschriebene Prozesslogik des Stored Process prüft die Datenmenge, die sich aus der Abfrage ergibt. Abbildung 3 veranschaulicht die konsekutive Folge der einzelnen Tests. Diese sind im Code als „if“ bzw. „else if“-Bedingungen hinterlegt. Der gesamte Prozess hat drei Szenarien:

Szenario 1: Keine Daten vorhanden. Wenn die Anzahl der Zeilen, die eine Abfrage produziert, gleich Null ist (pNumRows = 0), wird die HTML-Meldung "No data available for selected values!" ausgegeben.

Szenario 2: Es sind Daten vorhanden und das Zeilenlimit wird nicht überschritten. In diesem Fall erfolgt direkt die Ausgabe der Daten als CSV-Format zusammen mit einem HTML-Report in einer ZIP-Datei.

Szenario 3: Die Abfrage überschreitet das Zeilenlimit. Wenn das Zeilenlimit überschritten wird, gibt der Prozess eine Meldung an den Nutzer zurück. Der Nutzer kann anschließend die maximale Anzahl an Zeilen herunterladen. Hierfür wird eine Markierung in die Variable pNumRowsFLG gesetzt (pNumRowsFLG = „Y“). Dieser Wert verursacht, dass bei Klick auf Download die Zeilenanzahl (pNumRow) auf „1“ gesetzt wird und somit die Daten heruntergeladen werden.



Die Markierungen und Werte der Zeilen sind in Abbildung 3 als grüne Parallelogramme dargestellt.

Abbildung 3: Prozesslogik des Stored Process

Initialize Variables

NRNRFLG

Test:NRFLG ≠

Y

Test:NR = 0

Message: „No Data available.“

Test:NR >

100.000

Message: „Too much

data.“

Bei Klick auf Download:

Extrahiere Daten als CSV und erstelle ZIP-

Datei zum herunterladen.

Setze NR = 0,Zähle Zeilen der ver-fügbaren Daten und

schreibe Anzahl in NR.

Setze NRFLG = Y und starte Programm von

vorne.Extrahiert 1.000.000 Zeilen der Auswahl.

Setze NR = 1

NR = 1

NRFLG = Y

NRFLG = Y

NRFLG ≠ Y

NR = 0

NR ≠ 0

NR > 1.000.000

NR < 1.000.000

Prozesslogik SAS Stored Process

Abkürzungen:NR = Number of Rows (pNumRows)NRFLG = Number of Rows Flag (pNumRowsFLG)

NR = ?



Detaillierte Betrachtung des Codes:

Auf den folgenden Seiten wird der Code des Stored Process in zehn Abschnitten betrachtet:

Codeabschnitt 1

Der erste Abschnitt dient zur Vorbereitung der Abfragen durch Einstellen des Datenbank-Zugriffs und notwendiger Macros.

Zeile 2 markiert den Start des Macros „report“. Die Definition wird in Codeabschnitt 10 mit „%mend report“ beendet.

Zeile 4 startet eine neue Session. Zeile 6 und 7 erstellen die Parametervariablen „pNumRows“ und „pNumRowsFLG“. Diese werden

in der Prozesslogik für die „if“ bzw. „if else“-Bedingungen zur Bestimmung der Datenmenge und als Markierung zu großer Datenmengen verwendet.

Zeile 9 zeigt das verwendete libname-Statement des Beispiels an.

1. /* Macro 1: report() */ 2. %macro report();

3. /* Start session.*/ 4. %let rc=%sysfunc(stpsrv_session(create));

5. /* Global variables needed for query. */ 6. %global pNumRows; 7. %global pNumRowsFLG;

8. /* Library. */ 9. LIBNAME M_SHELP BASE "/sas/sasinstall/SASFoundation/9.4/sashelp";



Codeabschnitt 2

In diesem Codeabschnitt wird geprüft, ob der Stored Process zum zweiten Mal durchläuft.

Zeile 2 prüft, ob die Markierung "&pNumRowsFLG." ungleich „Y“ ist. Wenn die Bedingung wahr ist, werden die Zeilen 4 bis 13 ausgeführt. "&pNumRowsFLG." erhält den Wert „Y“ zu einem späteren Zeitpunkt im Code (Abschnitt 10), wenn eine zu große Datenmenge abgefragt wird und der Nutzer bestätigt, dass die maximale Anzahl an Zeilen heruntergeladen werden soll.

Zeile 4 initialisiert die Variable „numRows“ mit „0“. Zeilen 6 bis 10 erstellen eine Abfrage auf die ausgewählte Tabelle und nutzten eine „where“-

Bedingung mit den eingegebenen Parametern. Zusätzlich wird die Abfrage auf das eingestellte Datumsintervall beschränkt. Die Anzahl der Zeilen dieses Ergebnisses wird gezählt und als „numRows“-Variable gespeichert.

Zeilen 14 bis 17 stellen den alternativen Fall der Abfrage aus Zeile 2 dar. In diesem Fall ist die Abfrage "&pNumRowsFLG." = "Y" wahr. Damit wird die Variable „numRows“ gleich „1“ gesetzt. Dieser Schritt dient dazu, dem Nutzer die Option zu bieten, das maximale Limit der Daten auch dann herunter laden zu können, wenn die eingestellten Parameter eine zu große Datenmenge ergeben.

1. /* Check if STP runs second time with flag for limited data. */ 2. %if "&pNumRowsFLG." NE "Y" %then %do; 3. /* Initialize variable. */ 4. %let numRows=0;

5. /* Check number of rows.*/ 6. proc sql noprint; 7. select count(*) into :numRows 8. from M_SHELP.MDV 9. where created_date between "&pInterval_Min."d and

"&pInterval_Max."d 10. and CODE = "&pCode" 11. and ORIGCITY = "&pOrigCity" 12. ; 13. quit; 14. %end; 15. %else %if "&pNumRowsFLG." = "Y" %then %do; 16. /* Initialize variable. */ 17. %let numRows=1; 18. %end;



Codeabschnitt 3

In Codeabschnitt 3 wird überprüft, ob bei der Abfrage Daten verfügbar sind.

Zeile 2 testet, ob die Macro-Variable „&numRows“ gleich „0“ ist. Wenn dies wahr ist, werden die Zeilen 4 bis 14 ausgeführt.

Zeile 4 und 5 legen die Systemoption ODSDEST auf das HTML-Format fest. Die in den Zeilen 6 bis 8 erstellte Nachricht an den Nutzer wird somit im HTML-Format ausgegeben.

Zeile 6 bis 8 definieren die Nachricht an den Nutzer und speichern diese als work.report. Zeile 9 bis 14 verwenden die soeben in work.report gespeicherte Nachricht und geben diese

mittels proc report im zuvor bestimmten HTML Format aus.

1. /* Check if data is available and within limits."*/ 2. %if &numRows. = 0 %then %do; 3. /* No data. */

4. %global _ODSDEST; 5. %let _ODSDEST=HTML;

6. data work.report; 7. Message= "No data available for selected values!"; 8. run;

9. proc report data=work.report noheader

style(report)=[borderwidth=0]; 10. define message / display; 11. compute message; 12. call define(_row_,'style','style={borderwidth=0

foreground=cxff0000}'); 13. endcomp; 14. run;

15. %end;



Codeabschnitt 4

Dieser Abschnitt setzt die „if“-Bedingung aus Codeabschnitt 3 fort. Der Test wird mit „%else %if“ fortgesetzt und prüft nun, ob weniger als 1.000.000 Zeilen ausgewählt wurden oder die Markierung „&pNumRowsFLG“ = „Y“ zutrifft. Ist eine der Bedingungen wahr, wird die Abfrage in den Zeilen 4 bis 19 ausgeführt.

Zeile 4 leitet die Abfrage ein und beinhaltet eine weitere „if“-Bedingung. Diese Bedingung legt fest, dass bei "&pNumRowsFLG." = "Y” die Anzahl der Ausgabezeilen auf 1.000.000 festgelegt wird, was somit auch die maximale Datenmenge darstellt, die in diesem Prozess gespeichert werden darf.

Zeile 5 erstellt die temporäre Zieltabelle als „work.data“. Diese Tabelle enthält Daten, die in den Codeabschnitten 8 und 9 gespeichert werden.

Zeile 6 und 7 geben Spalten für die Ausgabetabelle an. Zeile 8 bis 13 beziehen die in den Parametereinstellungen ausgewählten Spalten in die Abfrage

ein. Wenn mehr als eine Spalte ausgewählt wurde, gibt Zeile 10 vor, dass ein Loop gestartet wird. Dieser Loop iteriert durch die Anzahl der gezählten Spalten und fügt diese in Zeile 9 hinzu. In Zeile 11 wird der alternative Fall, die Selektion einer einzelnen Spalte, verarbeitet.

Zeile 14 definiert die Quelltabelle. Zeile 15 bis 18 selektieren die Daten mit der Einschränkung über das Datumsintervall durch die

Parameter "&pInterval_Min."d und "&pInterval_Max."d, sowie der beiden Parameter „pCode“ und „pOrigCity“.

1. %else %if &numRows. < 1000000 or "&pNumRowsFLG." = "Y" %then %do; 2. /* Data available and within limits. */

3. /* Query data. */ 4. proc sql noprint %if "&pNumRowsFLG." = "Y" %then outobs=1000000;; 5. create table work.data as 6. select MDV_EXP.CODE, 7. MDV_EXP.ORIGCITY 8. %if &pTableCols_count > 1 %then %do i=1 %to

&pTableCols_count %by 1; 9. , MDV_EXP.&&pTableCols&i. 10. %end; 11. %else %do; 12. , MDV_EXP.&pTableCols 13. %end; 14. from M_SHELP.MDV as MDV_EXP 15. where SHIPDATE between "&pInterval_Min."d and

"&pInterval_Max."d 16. and CODE = "&pCode" 17. and ORIGCITY = "&pOrigCity" 18. ; 19. quit;



Codeabschnitt 5

Dieser Abschnitt erstellt diejenigen Informationen, die in der Eingabemaske für die Parameter der Datenspeicherung angezeigt werden. Dazu werden die individuellen (distinct) Werte der relevanten Parameter aus der work.data-Tabelle selektiert. Zusätzlich werden das früheste und das späteste Datum mit verfügbaren Daten erstellt und als Variable gespeichert. In den letzten Zeilen erfolgt eine Formatierung des Datums zweier Intervallvariablen.

1. /* Query prompt information.*/ 2. proc sql noprint; 3. select distinct CODE into :codes separated by ',' 4. from M_SHELP.MDV 5. where CODE = "&pCode" 6. and ORIGCITY = "&pOrigCity" 7. order by 1 8. ; 9. select distinct ORIGCITY into :origcities separated by ',' 10. from M_SHELP.MDV 11. where CODE = "&pCode" 12. and ORIGCITY = "&pOrigCity" 13. select count(*) format=commax20. into :rows 14. from work.data 15. ; 16. select min(created_date) format=yymmddd10. into

:pInterval2_min 17. from UC_DEXP.&pTable. 18. where created_date between "&pInterval_Min."d and

"&pInterval_Max."d 19. and CODE = "&pCode" 20. and ORIGCITY = "&pOrigCity" 21. ; 22. select max(created_date) format=yymmddd10. into

:pInterval2_max 23. from UC_DEXP.&pTable. 24. where created_date between "&pInterval_Min."d and

"&pInterval_Max."d 25. and CODE = "&pCode" 26. and ORIGCITY = "&pOrigCity" 27. ; 28. quit;

29. data _null_; 30. date1=put("&pInterval_Min."d, yymmddd10.); 31. date2=put("&pInterval_Max."d, yymmddd10.); 32. call symput('pInterval_Min', date1); 33. call symput('pInterval_Max', date2); 34. run;



Zeile 2 leitet die Abfrage ein. Die Option „noprint“ wird genutzt, um eine Ausgabe zu unterdrücken.

Zeile 3 bis 14 selektieren und sortieren die individuellen (distinct) Werte der Versandcodes und der Städtenamen als neue Variable aus der work.data-Tabelle. Die „where“-Bedingung umfasst dabei jeweils die Parameter „pCode“ bzw. „pOrigCity“.

Zeile 16 bis 20 selektieren das früheste (min) Datum für verfügbare Daten aus der Tabelle, die im Parameter „&pTable“ hinterlegt ist. Dafür wird sowohl nach dem Intervall zwischen "&pInterval_Min."d und "&pInterval_Max."d als auch nach den Parametern „pCode“ und „pOrigCity“ gefiltert.

Zeile 27 bis 32 nutzen einen Data Step mit der Option „_null_“, um eine Ausgabe zu erzeugen, ohne dass dabei ein Datensatz angelegt wird. Mit dem Data Step werden die Intervalle in ein Datumsformat umgewandelt.

Codeabschnitt 6

Dieser Abschnitt definiert die Selektionsparameter für die Dokumentation des Datenexports als HTML-Dokument.

1. /* In this section the Selection parameters in the promt-window are

defined.*/ 2. data work.prompts; 3. attrib name length=$30 format=$30.; 4. attrib value length=$2000 format=$2000.; 5. name="Delivery Code"; 6. value="&codes."; 7. output; 8. name="Origin City"; 9. value="&origcities” 10. output; 11. name="Time Interval Selected"; 12. value="&pInterval_Min. - &pInterval_Max."; 13. output; 14. name="Time Interval Available"; 15. value="&pInterval2_Min. - &pInterval2_Max."; 16. output; 17. name="Table Name"; 18. value="&pTable."; 19. output; 20. name="Table Columns"; 21. value="%expStpPromptValues(pTableCols)"; 22. output; 23. name="Number of Rows"; 24. value="&rows."; 25. output; 26. run;



Zeile 2 leitet den Data Step ein. Zeile 3 und 4 assoziieren ein Format mit der jeweiligen Variable (name bzw. value). In diesem Fall

wird die Länge und das Format der im Einstellungsfenster angezeigten Parameter festgelegt. Zeile 5 bis 26 weisen jeder eingestellten Variable neben ihrem Namen die entsprechenden Werte

aus der jeweiligen Macro-Variable zu.

Codeabschnitt 7

Ab diesem Abschnitt wird die Ausgabe der CSV-Datei vorbereitet. Hierfür wird zunächst ein Zeitstempel generiert, welcher anschließend in den Dateinamen integriert wird.

Zeile 3 ermittelt den aktuellen Zeitstempel und schreibt diesen in die dttm-Variable. Zeile 5 bis 8 senden den zusammengesetzten Dateinamen an den Browser. Zeile 9 schließt alle offenen ODS Ausgabeziele.

1. /* Output. */ 2. /* Set timestamp for filename. */ 3. %let dttm=%sysfunc(datetime(),b8601dt.);

4. /* Send ZIP header to browser. */ 5. data _null_; 6. rc = stpsrv_header('Content-type','application/zip'); 7. rc = stpsrv_header('Content-disposition',"attachment;

filename=EXP_Data_Export_&dttm..zip"); 8. run;

9. ods _all_ close;



Codeabschnitt 8

In diesem Abschnitt wird der Pfad zum Abspeichern der exportierten Daten erstellt und die CSV-Datei generiert.

Zeile 2 speichert den Zielpfad und den Dateinamen für den Datenexport als „o_data“-Variable. Zeile 3 öffnet ein neues Ausgabeziel für das ODS mit der Option „csv“ und legt „;“ als

Trennzeichen fest. In Zeile 6 wird das ODS Ausgabeziel wieder geschlossen. Zeile 4 bis 5 produziert die Ausgabe und unterdrückt mit der Option „noobs“ die Anzeige des

Resultats. Zeile 7 bis 8 erstellen eine Ausgabeanzeige als HTML-Datei. Zeile 9 bis 12 kompilieren die dazugehörigen Namen und Werte der Anzeige aus dem

„work.prompts“-Datensatz, der in Codeabschnitt 6 erstellt wurde. Zeile 13 schließt das ODS-Ausgabeziel für das HTML.

1. /* CSV output. */ 2. filename o_data

"%sysfunc(pathname(work))/MDV_Data_Export_&dttm..csv"; 3. ods csv file=o_data options(delimiter=';');

4. proc print data=work.data noobs; 5. run;

6. ods csv close;

7. filename o_prompt "%sysfunc(pathname(work))/MDV_Data_Export_&dttm..html";

8. ods html file=o_prompt;

9. proc report data=work.prompts; 10. define name / display 'Prompt Name'; 11. define value / display 'Selected Values'; 12. run;

13. ods _all_ close;



Codeabschnitt 9

Nachdem im vorherigen Abschnitt der Pfad und der Dateiname zur Variablen „o_data“ zusammengefasst und die HTML-Ausgabe „o_prompt“ definiert wurden, wird in Abschnitt 9 die ZIP-Datei erstellt und an den Browser übergeben.

Zeile 2 beginnt den Erstellungsvorgang der ZIP-Datei. Die Option „nopf“ verhindert, dass eine Beschreibung zum Inhalt der ZIP-Datei angelegt wird.

Zeile 3 und 4 fügen die Daten aus „o_data“ und „o_prompt“ zum Vorgang hinzu. Zeile 5 bis 7 erstellen die ZIP-Datei. Dazu wird die „publish archive“-Option genutzt. Zeile 8 beendet den Vorgang und entfernt das Package Objekt „newzip“. Zeile 10 referenziert die soeben erstellte ZIP-Datei als „in“. Zeile 11 bis 17 nutzen einen Data Step mit Option „_null_“, wodurch die Ausgabe erzeugt wird,

ohne einen Datensatz anzulegen. Mit dem Statement „length“ in Zeile 12 wird die Anzahl der Bytes für „data“ auf 1 begrenzt. Damit wird immer ein Byte einzeln eingelesen und an den Browser weitergeleitet.

Zeile 13 legt das Eingabeformat für „in“ fest. Die Host-Option „recfm“, die das Datensatzformat bestimmt, wird in diesem Fall auf „n“ und damit auf ein binäres Format gesetzt. Das Einlesen der Datei erfolgt somit in Bytes und ohne Begrenzung der Zeilenlänge.

Zeile 14 spezifiziert die Ausgabedatei für das put-Statement in Zeile 16. Die Referenz wird auf „_webout“ festgelegt und das Format, wie für die Eingabe bereits, mit „recfm“ auf binär („n“) gesetzt. Mit der Option „mod“ werden die Ausgabezeilen an bereits existierende Zeilen angefügt.

Zeile 15 legt fest, in welchem Format die Daten ausgegeben werden sollen. In diesem Fall wird das Format auf „$char1“ festgelegt. Die „@@“ Zeichen am Ende der Zeile sorgen dafür, dass der IML Pointer (IML = Interactive Matrix Language) am Ende des entsprechenden Datensatzes

1. /* Create ZIP. */ 2. ods package(newzip) open nopf; 3. ods package(newzip) add file=o_data; 4. ods package(newzip) add file=o_prompt; 5. ods package(newzip) publish archive properties( 6. archive_name="output.zip" 7. archive_path="%sysfunc(pathname(work))"); 8. ods package(newzip) close;

9. /* Send file to browser. */ 10. filename in "%sysfunc(pathname(work))/output.zip"; 11. data _null_; 12. length data $1; 13. INFILE in recfm=n; 14. file _webout recfm=n mod; 15. input data $char1. @@; 16. put data $char1. @@; 17. run; 18. %end;



gehalten wird. Dadurch können mit zusätzlichen input-Statements nochmals weitere Datensätze aus derselben Zeile gelesen werden können.

Zeile 16 schreibt die Datenausgabe in das zuvor in Zeile 14 definierte Ausgabeziel „_webout“. Dabei wird, wie in Zeile 15, der IML-Pointer am Ende des jeweiligen Datensatzes gehalten.

Zeile 18 schließt den Prozess, welcher in Codeabschnitt 4 durch die Bedingung „%else %if &numRows. < 1000000 or "&pNumRowsFLG." = "Y" %then %do;“ angestoßen wurde, ab.

1. %else %if &numRows. > 1000000 %then %do; 2. /* Data available but not within limits. */

3. %global _ODSDEST; 4. %let _ODSDEST=HTML; 5. %stpbegin;

6. /* Create message with link. */ 7. data work.output; 8. attrib message length=$1000 format=$1000.; 9. message="You reached the export limit of 1.000.000 rows.

Please modify your selection or download the reduced data.";

10. output; 11. message="Download file";

12. output; 13. run;

14. /* Output error message. */ 15. proc report data=work.output noheader

style(report)=[borderwidth=0]; 16. define message / display; 17. compute message; 18. call

define(_row_,'style','style={borderwidth=0}'); 19. endcomp; 20. run;

21. %stpend; 22. %end;

23. %mend report; 24. %report;



Codeabschnitt 10

Im letzten Codeabschnitt wird die alternative Möglichkeit zum Fall in Codeabschnitt 4 abgebildet. Überschreitet die Anzahl der Zeilen aus der vom Nutzer eingestellten Abfrage 1.000.000, so wird eine Nachricht an den Nutzer gesendet und die Option zum Herunterladen der maximal möglichen Anzahl an Zeilen in einem Link ausgegeben.

Zeile 1 legt die Bedingungen für den darunter folgenden Ablauf fest. Wenn „&numRows“ mehr als 1.000.000 Zeilen zählt, wird der Prozess durchgeführt.

Zeile 3 bis 7 sind analog zu Codeabschnitt 3 aufgebaut. Zeile 3 und 4 stellen das Ausgabeziel für den Ablauf auf HTML ein. Zeile 5 definiert den Anfang des Stored Process.

Zeile 7 bis 13 beschreiben Aufbau und Inhalt der HTML-Nachricht an den Nutzer. Dabei wird mit einem Data Step in Zeile 7 zunächst work.output definiert. Die Abmessungen der Nachricht wird mit „attrib“ in Zeile 8 festgelegt. Zeile 9 beinhaltet den Text für die HTML-Nachricht, während Zeile 11 einen Button mit der Aufschrift „Download file“ zur Verfügung stellt. Die Funktion dieses Buttons ist, den Parameter „pNumRowsFLG“ auf den Wert „Y“ zu setzen und das Programm erneut aufzurufen. Da die in Codeabschnitt 4 beschriebene Bedingung den Parameter „pNumRowsFLG“ nun mit dem Wert „Y“ auffindet, wird die ZIP-Datei erzeugt, obwohl die Anzahl der Zeilen größer als 1.000.000 ist.

Zeile 14 bis 20 geben die Fehlernachricht aus. Dazu wird „proc report“ verwendet. Zeile 16 weist mit „define“ das Report-Item „message“ zu. Die Nachricht wird in Zeile 17 bis 19 mit style-Einstellungen formatiert.

Zeile 23 schließt die Definition des Macros „report“ ab. Zeile 24 ruft das Macro „report“ auf.

Stored Process Einstellungen

Für die Eingabeparameter „Code“, „OriginCity“ und „TableColumns“ wurde die MDV-Tabelle als Quelle für Auswahlwerte selektiert.

Damit der Stored Process im Browser korrekt ausgeführt wird, muss unter den Stored-Process-Einstellungen im Reiter „Execution Options“ die Option „Result capabilities“ auf „Stream“ und „Package“ gesetzt werden. Zusätzlich ist im Reiter „SAS Code“ im Dropdown-Menu „Include code for“ die Option „Stored process macros“ zu deaktivieren.

Das hier demonstrierte Beispiel verwendet die Option „Single values“ bezüglich der Anzahl der auswählbaren Werte pro Parameter für die Variablen „Code“ und „OriginCity“. Damit mehrere Werte ausgewählt werden können, erfordert es eine Erweiterung des zuvor beschriebenen SAS® Codes in den „where“-Bedingungen. Diese müssen darauf ausgelegt werden, nach mehreren Werten eines Parameters zu filtern.

Stored Process Ausführung

Wird der Stored Process im Browser ausgeführt, öffnet sich die Oberfläche zur Einstellung der Parameter. Abbildung 4 zeigt die Auswahl der Spalten für die Export-Datei:



Abbildung 4: Eingabefenster für Parameter bei Ausführen des Stored Process

Wenn der Stored Process eingestellt und mit Klick auf „Run“ durchgeführt wurde, erstellt der Prozess die ZIP-Datei und lädt diese herunter. Die ZIP-Datei enthält die Abfrageergebnisse als CSV-Datei und das HTML-Dokument mit den Angaben über die durchgeführte Abfrage, wie in Abbildung 5 gezeigt wird.



Abbildung 5: Ergebnisse der Abfrage und Inhalt der ZIP-Datei

Fazit

Der in diesem White Paper vorgestellte Stored Process bietet Nutzern, die mit SAS® nicht vertraut sind, eine einfache und effiziente Möglichkeit, Abfragen und Datenexporte durchzuführen. Die browserbasierte Abfrage wird im HTML-Format dokumentiert, womit die Einstellungen der Abfrage auch später nachvollziehbar bleiben. Das im Stored Process einprogrammierte Limit verhindert zu große Abfragen.

Der Stored Process kann leicht erweitert werden, um beispielsweise Parameter mit mehreren Auswahlwerten zuzulassen, weitere Parameter einzubeziehen oder eine Abfrage auf mehrere Tabellen auszuführen. Er stellt damit ein nützliches Werkzeug zur Datenextraktion für eine breite Nutzergruppe dar, ohne SAS® Kenntnisse vorauszusetzen.



Kontaktieren Sie uns //

mayato GmbH Friedrichstraße 121 10117 Berlin

[email protected]

+49 / 30 7001 4692-51

Was wir vorhersagen, soll auch eintreffen!

mailto:[email protected]

ZusammenfassungSchlüsselwörterZielgruppeGrundlegende InformationenWas wird für das demonstrierte Beispiel benötigt?Server Anforderungen:Client Anforderungen:

Funktion des SAS® Stored ProcessGrundstruktur des SAS® CodesDetaillierte Betrachtung des Codes:Codeabschnitt 1Codeabschnitt 2Codeabschnitt 3Codeabschnitt 4Codeabschnitt 5Codeabschnitt 6Codeabschnitt 7Codeabschnitt 8Codeabschnitt 9Codeabschnitt 10

Stored Process EinstellungenStored Process AusführungFazitKontaktieren Sie uns //

Datenexport mittels SAS Stored Process - mayato€¦ · mayato GmbH – – [email protected] White...

Documents

Transcript of Datenexport mittels SAS Stored Process - mayato€¦ · mayato GmbH – – [email protected] White...