SAS Stored Processes and Developing Web Applications Phil Mason - - PowerPoint PPT Presentation
SAS Stored Processes and Developing Web Applications Phil Mason Independent SAS Consultant Contents Creating a Simple Web App using Enterprise Guide Make a report Turn it into a Stored Process Use Stored Process Web App Make
SAS Stored Processes and Developing Web Applications
Phil Mason Independent SAS Consultant
Contents
Creating a Simple Web App using Enterprise Guide
Make a report Turn it into a Stored Process Use Stored Process Web App
Make a better Web App
Add some HTML Add Java Script
Make the best Web App
Use a Java Script framework
Make a report
Use Enterprise Guide Build a query
Query Builder
Query builder
Now you have a query
Make a Stored Process
Name it
View code
Pick Repository
Librefs
Prompts
Data Sources & Targets
Review
Edit Stored Process
Stored Process
Composed of:
A metadata component A code component
Alternative way to create a Stored Process
Create the code with an editor In Management Console create the metadata/code
Let’s take a quick look at Management Console
Create using Management Console
Name it
Execution settings
Select server to run it on Specify we want to stream output and that we can produce a package Keep code in metadata
Edit Source Code
Define parameters
Run it via Enterprise Guide
Edit a Stored Process with Management Console
Find the one you want and double click it
Management Console
Name & Location
Management Console
Management Console
Management Console
Management Console
Management Console
Management Console
Stored Process Web Application
Part of Integration Technologies Allows running a stored process
From a web browser From an application such as EXCEL, MS Word
Parameters passed on URL appear as macro variables to Stored Process
Stored process web application
Get a menu down left
Run stored process from web app
Run stored process from browser
Run stored process from excel
Open one to run it
Results of run appear in EXCEL
Add a Graph to Stored Process
Result in web browser
Add parameter
Add prompt for macro parameter added
Stored Process using parameter via prompt
URL showing parameter passed
http://my.server/SASStoredProcess/do? _program=%2FShared+Data%2FSASTesting %2FTest3&product_line=Children
Make a better Web App
Use some HTML
Gives us some more control
Use some JavaScript
Great for adding more interactivity Allows programming to control User Interface
Simple HTML
<html> <body> <h1>Pick a report to run</h1> <a href="http://my.server/SASStoredProcess/do? _program=%2FShared+Data%2FSASTesting%2FTest3 &product_line=Children"> Children</a><p> <a href="http://my.server/SASStoredProcess/do? _program=%2FShared+Data%2FSASTesting%2FTest3 &product_line=Clothes+%26+Shoes">Clothes & Shoes</a><p> <a href="http://my.server/SASStoredProcess/do? _program=%2FShared+Data%2FSASTesting%2FTest3 &product_line=Outdoors">Outdoors</a><p> <a href="http://my.server/SASStoredProcess/do?_program=%2FShared+Data%2FSASTesti ng%2FTest3 &product_line=Sports">Sports</a><p> </body> </html>
HTML Menu
Report shown in browser
Using a form to run a Stored Process
HTML form is great for making User Interfaces Form tag
Make the first part of URL
Input tag, using different type settings
Text – fields for text Password – passwords where text is hidden Radio – radio buttons Checkbox – check boxes Submit – submit buttons to execute URL using parts of form
HTML using Form
What’s displayed
Writing directly from Stored Process to browser
_webout
Reserved fileref for writing from Stored Process to browser %stpbegin macro sends output from procs to _webout %stpend stops output going to _webout Can write from a datastep to _webout
Best to turn automatic Stored Process macros off
New stored process to write HTML
Stored process produces this
Add iFrame to keep output on same page
Now menu & output on same page
Useful optional parameters for Stored Processes
_odsdest – lets us choose an ODS destination for type of
Switch between HTML, RTF, PDF, EXCEL, etc.
_odsstyle – choose an ODS style _debug – can produce the SAS log, execution times, etc.
_debug=log,time _debug=2179 … this is my favourite
Define a dropdown menu for type of output
Output type is added to menu
HTML Headers
Set headers in HTML from stored process Indicates what browser should do RTF EXCEL
rc = stpsrv_header("Content-type", "application/msword") ; rc = stpsrv_header('Content-disposition','attachment; filename=test.rtf'); rc = stpsrv_header("Content-type", "application/pdf") ; rc = stpsrv_header('Content-disposition','attachment; filename=test.pdf');
RTF output
Define dropdown for ODS style
Here is what 2 ODS style selections produce
Add some checkboxes
After report we get the log
After log we get time
Parts of URL for stored process call
http://khv-sas- iis.cfsi.local/SASStoredProcess/do? _program=%2FShared+Data%2FSA STesting%2FTest3&product_line=Chi ldren &_debug=log&_debug=time
Useful automatic macro variables
Use automatic macro variables
Construct a URL using:
&_srvname &_srvport &_url
What’s good about this technique
Easily make SAS code run from a web browser Generate HTML, Excel, PDF or RTF easily. Easily add parameters making it much more flexible Build up a multiple page Web Application
What’s not so good
Tables and graphs don’t look as good as they could
Can improve their look with some more work within SAS
Tables lack some functionality
Cant sort columns Slow using lots of data Table headers are not fixed, so they will scroll away if tables are long
Only some basic widgets available for building a UI Only basic use of mouse for selecting
Adding some JavaScript for interactivity
onLoad – execute when page finishes loading onUnload – execute when page closes onKeyPress – execute when a key is pressed onKeyUp – execute when a key is released onMouseOver – execute when mouse is over something onMouseOut - … when mouse moves away onMouseDown … when left button is pressed onMouseUp … when left button is released onClick … when left button is clicked onDblClick … when left button is double clicked
JavaScript Demo
Lets see some of those things… Note: not all browsers support all JavaScript (in the same way)
Try things out in all the browsers you might use
<html> <head> <script type='text/javascript'> var popwin = null; function open_popup() { if (popwin == null) popwin = window.open( 'http://www.google.com', '', 'top=150, left=350, width=250, height=50, status=0, toolbar=0, location=0, menubar=0, directories=0, resizable=0, scrollbars=0' ); } function close_popup() { if (popwin != null) { popwin.close(); popwin = null; } } function checkKey() { var key = String.fromCharCode( window.event.keyCode); if (key == 'X') { alert( "You pressed the X key" ); } } </script>
Some added JavaScript for extra functionality
JavaScript providing more interactivity
</head> <body onload='alert("finished loading");' onunload='alert("finished unloading");'
String.fromCharCode(window.event.keyCode) ;'
Pop-up a window with information by moving over <a href='#'
<p>Pop-up a window with information by holding mouse button down <a href='#'
<p><a href='#' ondblclick='open_popup();'>Double click to open</a>, <a href='#' onclick='close_popup();'>single click to close here</a>. <p><a href='#' style='font-size="x-large"' onmousemove='open_popup();'>Move mouse over this to open</a>, <a style='font-size="x-large"' href='#' onmousemove='close_popup();'>move
<p>Press <b>X</b> to make an alert window pop up. <p>Hold down a key to see what it is in the status bar. </body> </Html>
Stored Process generates HTML
Some excerpts…
<form method="get" action="http://khv-sas-iis2.cfsi.local/SASStoredProcess/do?" target="content"> <select name="_program"> <option value="/Shared Data/SASTesting/cabotCollectionsDashboard">Month to Date</option> <option value="/Shared Data/SASTesting/ccDashDaily">Daily</option> <option value="/Shared Data/SASTesting/ccDashYTD">Year to Date</option> </select> <input type="checkbox" name="_debug" value="log">Show log<nbsp><nbsp><nbsp> <input type="checkbox" name="_debug" value="time">Show time taken<nbsp> <input type="checkbox" name="inline" value="1">Inline?<nbsp> <input type="submit" value="Run"><br>
More excerpts of HTML
<option value="Cabot Debt Purchase " selected>Cabot Debt Purchase </option> <option value="Cabot Stratford Serviced ">Cabot Stratford Serviced </option> </select> </form> <iframe name="content" height="85%" width="100%"> </iframe> </body> </html>
A few good JavaScript libraries
jQuery
Write less, do more
HandsOnTable or EasyUI
Excel-like spreadsheet for Apps
HighCharts
Create interactive web charts easily
D3
Data Driven Documents
Manipulating your web page
jQuery makes it easy to select parts of your page and attach functionality
$(document).ready(function(){ $("button").click(function(){ $("p").hide(); }); }); $(document).ready(function(){ $("button").click(function(){ $("#div1").fadeIn(); $("#div2").fadeIn("slow"); $("#div3").fadeIn(3000); }); }); <script> $(document).ready(function(){ $("button").click(function(){ $("#div1").load("demo_test.txt"); }); }); </script> <div id=“div1"> <h2>Let jQuery AJAX Change This Text</h2> </div> <button>Get External Content</button>
Making a better table in web browsers
Making interactive charts
Creating Data Driven Documents
Hosted Libraries
You can download JavaScript libraries and keep on your site Or you can use a Hosted Library, such as Google’s read about it here https://developers.google.com/speed/libraries/
<script src=“https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/ jquery.min.js”></script>
Some host their own library
<script src=“http://d3js.org/d3.v3.min.js"> </script> <script src="http://code.highcharts.com/highcharts.js"> </script>
How to use SAS with JavaScript objects
HTML 5 structures your page CSS 3 controls the look and feel JavaScript handles the interactivity Data typically either hard-coded or provided in JSON, CSV, TSV, … SAS Stored Processes
Provide the data in CSV or JSON format using data steps or PROC JSON Create and customise the HTML, CSS & JavaScript Using data steps or PROC STREAM
What is JSON and why use it?
Key-Value pairs Man and Machine readable Nested if necessary Used by many JavaScript objects Is a subset of JavaScript Object Notation, which is how
Sometimes XML or CSV can be used Very flexible can represent complex nested hierarchies of data
Example
{ "name": "Phil Mason", "home": { "town": "Wallingford", "phone": 1491824891, "current home": true, "ages": [ 51, 46, 18, 16, 13 ] } }
Data Step to produce JSON
Can run from a stored process Write to _webout to stream to browser Can define macros, to make it flexible and easy to repeat Can handle very complex & nested JSON
Example data step making JSON
data _null_ ; set sashelp.class end=_end ; if _n_=1 then put '[' ; put '{' ; put '"Name":"' name +(-1) '",' ; put '"Sex":"' sex +(-1) '",' ; put '"Age":"' age +(-1) '",' ; put '"Height":"' height +(-1) '",' ; put '"Weight":"' weight +(-1) '"' ; put '}' ; if not _end then put ',' ; else put ']' ; run ;
[ { "Name":"Alfred", "Sex":"M", "Age":"14", "Height":"69", "Weight":"112.5" } , { "Name":"Alice", "Sex":"F", "Age":"13", "Height":"56.5",
PROC JSON
SAS 9.4 Easily produces JSON from SAS datasets Can produce more complex JSON as well as simple JSON Can use macros to write PROC JSON, making it very flexible
PROC JSON
Minimum code needed Produces default output
{ "SASJSONExport": "1.0", "SASTableData+CLASS": [ { "Name": "Alfred", "Sex": "M", "Age": 14, "Height": 69, "Weight": 112.5 }, { "Name": "Alice", "Sex": " F", "Age": 13, "Height": 56.5, "Weight": 84 },
proc json out=temp ; export sashelp.class ; run ;
Better PROC JSON code
Pretty - lays out the JSON clearly noSAStags - leaves out SAS metadata from the JSON
proc json out='/tmp/class.json' pretty nosastags ; export sashelp.class ; run ; [ { "Name": "Alfred", "Sex": "M", "Age": 14, "Height": 69, "Weight": 112.5 }, Produces this output
PROC STREAM
Processes text and expands macros within it Text is sent to an external file Can execute SAS code while processing the input stream using %sysfunc(dosub(fileref)) Can include text into stream for processing using %include filleref Files can be read into stream, but macros not processed using &streamdelim readfile filref Newlines can be forced in output using &streamdelim newline
Simple example
%macro cols(n) ; %do i=1 %to &n ; <td>&i</td> %end ; %mend cols ; filename out '/folders/myfolders/test.html' ; proc stream outfile=out ; BEGIN <table> <tr>%cols(4)</tr> </table> ;;;; <table><tr><td>1</td> <td>2</td> <td>3</td> <td>4</td></tr></table>
General Approach
Find an example
Pick a visualisation Close to something you want Could be changed to meet needs Could be combined with other things to meet needs Check it for suitability Some things are easier than others Is it free? Handle libraries Make sure you are pointing to a local copy or CDN Copy & Test locally
Number Display Example
<script type="text/javascript" src="http://cdnjs.cloudflare.com/ajax/libs/d3/3.5.5/d3.min.js"></script> <script type="text/javascript" src="http://cdnjs.cloudflare.com/ajax/libs/crossfilter/1.3.11/crossfilter.min.js"></script> <script type="text/javascript" src="http://cdnjs.cloudflare.com/ajax/libs/dc/1.7.3/dc.min.js"></script> <script type="text/javascript" src="../js/d3.js"></script> <script type="text/javascript" src="../js/crossfilter.js"></script> <script type="text/javascript" src="../js/dc.js"></script> http://dc-js.github.io/dc.js/examples/cust.html
Wrap in a Stored Process
Write code to _WEBOUT using Data step Proc Stream Mixture of both Leave out the %stpbegin & %stpend macros Call using Stored Process Web Application from browser
http://server57562.uk2net.com/SASStoredProcess/do?_program=%2FUse
Use Proc Stream with PRESCOL
What should we change?
Parameters So we can control what it does Data So it works using our data Libraries Often they point to resources on another web site Can copy to our web site Can use Content Delivery Network (CDN)
Parameters
Identify anything we want to alter If it is a one time change, then make the change If we want to change it from run to run, then it is a candidate for macros Identify anything we want to add, e.g. a title
Data
Identify where the data is coming from What format is it in? We will need to make our data in same format Consider options for reproducing data
How to change things?
Macro variables Replace shorter things we want to control Macro programs Replace longer things we want to control Make use of looping, conditions, dataset I/O, file I/O, etc.
Change width and height to a macro variable
Add corresponding parameters to Stored Process
Use parms in call to Stored Process
http://server57562.uk2net.com/SASStoredProcess/do?_program=%2FUser+Folde
Can just make a flat file Can make a stored process to deliver data Data Step Proc Stream
Make data into a CSV “file”
Make a Stored Process to deliver data
Modify Stored Process to use data from Stored Process
Use the d3.csv function to read in CSV data into a JavaScript array. Remove the hard-coded data Add a call to load data All code needs to be inside function Finish off function call at the end with })
Stored Process using macro parms and another Stored Process for data
Where have we go to?
Stored Process for Program Creates HTML, JavaScript, CSS Stored Process for Data Produces a stream of CSV data Possibly copies of libraries on our Infrastructure CSS JavaScript We have a working little JavaScript/SAS Stored Process Visualisation Web Application!
Next Steps
Add more graphs to these 3 based on the same data Parameterise the data used Change variables too Make them all Stored Process parameters Therefore they are also macro variables Change Stored Process making data to be general purpose by feeding a dataset name to it. Create several Stored Processes like this and combine them to make a dashboard.
Repeat object(s) to make a Dashboard
We don't have time for this, but basic steps are … Create a bunch of empty div sections in HTML 5 perhaps just with a H1 so you can see them Make some CSS to lay them out how you like easy to do accordions, tabs, sliding layouts, etc. Use some jQuery to fill the div sections use Ajax calls to do it Possibly add some drop down selections lists, etc. Use jQuery to add info from there to your Ajax calls That’s about it!
Conclusions
It’s easy to find great JavaScript visualisation software It’s easy to wrap it up and customise it with a Stored Process It’s easy to feed it with data from another Stored Process Combine some of these into a dashboard or application Put it all together and you have yourself a powerful cutting edge visualisation system.
Some great links to useful resources
http://www.robslink.com/SAS/Home.htm
http://www.visualisingdata.com/index.php/re sources/
http://www.jsgraphs.com/
http://www.graphviz.org/Gallery.php
http://www.highcharts.com/demo
http://www.amcharts.com/demos/
https://github.com/mbostock/d3/wiki/Gallery
http://dc-js.github.io/dc.js/
Some great diagrams