In my last post, I demonstrated how developers can use the new Visual Analytics API to easily create svg images of both entire Visual Analytics report tabs, as well as individual report graph objects.  This got me to thinking, would it be possible to use the same method to create images of all the graphs in a Visual Analytics report, and archive them into a zip file?  Well, the answer turns out to be YES!  With just a bit more code, this can be done; and in this article, I'll show you how!   Step 1: Get a list of your report's graph objects   The first thing we will need is a list of the Visual Analytics report object's unique names.  In my last post, we obtained this for a single graph object by opening the report and clicking the “Create link” option in the object's properties.  This works well for a single object, but what if our report has LOTS of graphs?  For this, it would be better if we could programmatically get a list of the graph objects and their respective names.     I've written a previous article on how to get this list by using SAS code which leverages PROC HTTP to make an API call to the Viya Reports API and get a report's content elements.  A (slightly modified) version of this SAS code is below:     /*retrieve service endpoint*/ %let BASE_URI=%sysfunc(getoption(servicesbaseurl)); %let reportID = <- report uri ->; /* create filenames to hold responses*/ filename rcontent temp; /* Make request */ proc http oauth_bearer=sas_services method="GET" url="&BASE_URI/reports/reports/&reportID/content/elements?characteristics=visualElement" /* place response in filenames */ out=rcontent; headers "Accept"="application/vnd.sas.collection+json"; run; /* read in response */ libname rcontent json; /* return only "Graph" elements */ data reportElements; set rcontent.items; where Type eq "Graph"; run; proc print data=reportElements; var name label; run; *Notice, the code above uses the same methodology as does the programs in my previous article, where there is a SAS macro variable "&reportID" that defines the unique uri of the Visual Analytics report we want to retrieve the list of graph elements from.     When we run this code using the URI for the example report "Retail Insights" (that comes installed with SAS Viya), the table reportElements is created and printed:     Great!  Now that we have our list of the report's graph objects (and most importantly, their unique names), we can use this in conjunction with the code from my last post to create svg image files from each of them.   Step 2: Creating the image files and packaging them up   For this article’s use case we will want to use the same method from my previous article of using the new Visual Analytics API to generate svg images; but with just a few modifications.  First, since we want to produce a single zip file that contains all of our generated svg files, we will need to add an ODS package statement to our code.  The ODS package statement is used to first create a package of files (svg files in our case) and then publish them to a zip file.  If you want to learn more about using the ODS package statement, here is a great paper on the topic.  Also, we need to decide where to place our output files.  For this article's example, I'm going to initially place both the svg files and zip file in the directory used by this SAS Session’s work library.  To retrieve the physical location of the work library we will use the %sysfunc macro and save its output to the macro variable 'workDir'.  We will also need to give the final output zip file a name.  For our current example, I've chosen "myZipArchive.zip" and placed it in the macro variable "zipArchiveName".  For the creation of the SVG images themselves we want this part of the process to be automated.  Hence, we will wrap the code I demonstrated in my last post into a simple SAS Macro named "createVAReportObjectImage".  Additionally, we have added two lines to our macro's source code that add the output svg image to the ods package.   Finally, we can call the macro by submitting the line (but substituting the <-reportObjectName-> value for a report object that exists in your Visual Analytics report: %createVAReportObjectImage(&reportID.,<-reportObjectName->)   After we have called the macro one (or more) time(s) we close our ods package.       /*retrieve service endpoint*/ %let BASE_URI=%sysfunc(getoption(servicesbaseurl)); %let reportID = <- report uri ->; %let height=800px; %let width=600px; %let workDir = %sysfunc(getoption(work)); %let zipArchiveName = myZipArchive.zip; /* Create macro to retrieve reportObjects */ %macro createVAReportObjectImage(reportUri,reportObjectId); /* create the svg file */ filename imgfile "&workDir/myOutputImage_&reportObjectId..svg"; proc http method="GET" oauth_bearer=sas_services url="&BASE_URI/visualAnalytics/reports/&reportUri/svg?size=&height,&width&reportObject=&reportObjectId." out=imgfile; run; /* add the svg file to the ods zip archive */ ods package add file="&workDir/myOutputImage_&reportObjectId..svg" mimetype="application/x-compress"; ods package publish archive properties(archive_name="&zipArchiveName" archive_path="&workDir"); %mend; /* create ods package */ ods package open nopf; /* call the macro */ %createVAReportObjectImage(&reportID.,<-reportObjectName->) /* close the ods package */ ods package close;   When I run the code above on the example report "Retail Insights" for the two report object names ve179 and ve166, the code to call the macro looks like this:   /* call the macro */ %createVAReportObjectImage(&reportID.,ve179) %createVAReportObjectImage(&reportID.,ve166)   And that's it!  We have successfully created the svg images files, placed them in our work library's directory and ALSO created a zip file in our work library's directory.  If you would like to get a listing of these files you can use the following code which gets a list of your files from the work directory and only prints the zip files and files that have the text "myOutputImage" in the filename:    /* test to ensure that the file exists */ data filenames; length fref $8 fname $200; label fname = "Filename"; did = filename(fref,"&workDir"); did = dopen(fref); do i = 1 to dnum(did); fname = dread(did,i); output; end; did = dclose(did); did = filename(fref); keep fname; run; /* print the expected files */ title "Generated svg and zip files in the work library's directory"; proc print data=filenames noobs label; where index(fname,'myOutputImage')>0 or index(fname,'.zip')>0; run; title;   And the printed output looks like this:     Step 3: Adding All Generated SVG Files to the Archive   In step 2 we successfully created our zip archive.  However, we had to manually add the names of the VA Graph objects that we want to add.  This would be better if the program automatically added any graph objects it finds.  This piece is actually quite straight forward using the dataset we created in Step 1 and the CALL EXECUTE Routine.  In short, this routine allows us to execute SAS code that is located within the cell of a SAS Dataset.     So first, let's create the dataset with the needed SAS macro call to add each Visual Analytics graph object that is in the 'reportElements' dataset.  The code to do this is below:   data reportElementsCode; set reportElements; length code $ 500; code = '%createVAReportObjectImage(' || "&reportID." || ',' || trim(name) || ')'; keep name type label code; run;   Great!  As you can see, in the 'code' variable we have placed a call to the 'createVAReportObjectImage' macro and dynamically placed the uri of the report as well as the name of the graph object.  From here, it's a simple process of using the call execute routine to call our macro instead of manually calling it as we did at the end of Step2.  The code to do this (and have it wrapped in the proper ODS package statements) is below: ods package open nopf; data _NULL_; set reportElementsCode; call execute(code); run; ods package close; Great!  We've successfully automated our macro calls!   Step 4: Copying the Zip Archive to the Viya Files Service   You might have noticed in Step 2, I placed the generated zip archive in the work library's directory.  We probably want to copy this file to a better place before we end our SAS Studio Session.  You can choose any place you like.  For example, you might want to place the file in a directory which points to an internal git repo for your organization.  Or you might want to place it in an appropriately shared network directory so your team members can access it.  For this article, I'm going to use the FILENAME Statement: FILESRVC Access Method to save the output zip file to a folder in the Viya folders Content area.  Specifically, I'm going to place the zip file in the private "My Folder" area.  This is achieved using the following lines of code:   /* create the source filename */ filename src "&workDir/&zipArchiveName" recfm=n; /* create the destination filename using FILESRVC to the destination a zip file and folder */ filename dest filesrvc folderuri='/folders/folders/@myFolder' filename="&zipArchiveName" debug=http recfm=n; /* copy the file: output return code and any message */ data _null_; rc=fcopy("src","dest"); msg=sysmsg(); put rc=; put msg=; run; filename src clear; filename dest clear;   The code above uses the filesrvc method and the process described in this post to move the zip file from the work library's directory to the "My Folder" space Viya Content folder.   Wrapping Up   And that's it!  We have successfully written the needed SAS code to programmatically do the following:   Create a list of all the graph objects in a Visual Analytics report Create an SVG image file for each graph and place them in a zip file Save the final zip file to the "My Folder" area in the Viya Content Folders After running the code, the zip file appears in the "My Folder" area within the Viya Content Folders:     When we double click this zip file, SAS Studio offers a message that it can't open the zip file, but also allows us to download the file:     After downloading the file and unzipping it, we can see its contents:   From here we can open any of the image files using a browser of our choice:     And that's it!  In just a few lines of code we have programmatically created svg images of all the graph objects in a Visual Analytics report and archived them into a zip file!   *The code in this article is intended to be run against VA reports which have been developed using the Visual Analytics interface using CAS Data sources that are ACTIVELY loaded into memory.   How to make this example work for you   On GitHub, you will find all the code discussed in this article.  All code is intended to be executed within a SAS Studio session running in a SAS Viya 2021.2.4 (or later) environment which contains the SAS Viya services that are being called.  Additional code is included which will check first to ensure that the selected Visual Analytics report contains graph objects.  If no graph objects are found, the program gracefully exits.  Alternatively, if graph objects are found the svg images are created an archived.   On Github, you will find the following support files for this article: A SAS program which creates SVG image files of all graph objects within a Visual Analytics Report, places them into a zip archive named "myZipArchive.zip" and copies final zip file into in the private “My Folder” area for the user running the code - archiveReportImages.sas  Take Me to GitHub!   ... View more

I've written previously on how to use the reportImages API service to create images of SAS Visual Analytics reports.  The method I demonstrated on how to do this was a bit tedious, as it involved creating a job, observing the job's status, waiting for the job to complete, and then finally retrieving the location of the generated image.  This left me wondering if there was a simpler way to generate images of Visual Analytics reports.  Well, as it turns out there is!     As of SAS Viya version 2021.2.4, the new Visual Analytics API makes creating images of SAS Visual Analytics reports easier than ever!   Not only can it be used to create an image file of an entire report tab, but it can also create images of individual report objects.  In this article, I'll demonstrate how to call the Visual Analytics API from SAS Studio and use it to create an image of a Visual Analytics report – all in just 10 lines of code! An example of the process is below:     Let's get started!   Example 1: Creating Images of an Entire Report Tab   Before we can start generating the image of the report, we will need to know the following:   What is the id (or URI) of the Visual Analytics report that we want to create an image of? What size (height and width) do we want our output image size to be? Where do we want the output image placed? We can tell our SAS program the answers to these first 2 questions by simply entering them in a set of %let statements.  In the example code below, I've declared the report URI for the example report "Retail Insights" that comes installed with SAS Viya.  You can read more about the example reports in the SAS Viya Documentation.  I've also declared that I want my output image's height and width to be 800px and 600px respectively.   %let reportID = 6945298b-9466-41d3-aeed-f5a7aaf4985b; %let height=800px; %let width=600px; Great!  We are already well on our way!  Now where do we want the output image to be placed and what should its name be?  We can see from the API's documentation that the image which will be created is returned as the response’s body.  Because of this, we can use the FILENAME Statement: FILESRVC Access Method to save the output image to a folder in the Viya folders Content area.  For this article, I'm going to place the output image in the private "My Folder" area for my user id and name it "myOutputImage.svg".  This is achieved using the following line of code: filename imgfile filesrvc folderuri='/folders/folders/@myFolder' filename="myOutputImage.svg"; You may notice that I am using the value '/folders/folders/@myFolder' in the value of the ‘folderuri’ parameter.  This is a dynamic pointer that will automatically point to your "My Folder" directory in the SAS Content folder directory structure.  Alternatively, you could place your image in another location (such as "/Public" using the "FOLDERPATH=" option).   Alright!  Now that we have answered all the needed questions for creating the image file of our Visual Analytics report, we can start writing our API call using PROC HTTP.   First, we will need to retrieve the base URI for the service call.  Since we are doing our programming within SAS Studio, we can dynamically get this by submitting the following line of code.  The URI is saved to the SAS macro variable "BASE_URI":   %let BASE_URI=%sysfunc(getoption(servicesbaseurl));   With these pieces in place, we can write out our API call.  The code to make the call is below:     proc http method="GET" oauth_bearer=sas_services url="&BASE_URI/visualAnalytics/reports/&reportID/svg?size=&height,&width" out=imgfile; run; We begin our PROC HTTP call by specifying that we want to perform an HTTP "GET" method.  The next step is to think about authentication to the API.  Since we are already logged into the SAS Viya platform and submitting SAS code using SAS Studio, we can connect to the SAS Viya services by placing the keyword sas_services in the PROC HTTP option oauth_bearer. This ensures that the access token for the current authenticated user is automatically obtained and attached to the request.  Next, we specify the fully qualified URL path that identifies the endpoint for the HTTP request.  The pieces of this URL path were already saved as SAS Macro Variables in our %let statements.  So, we can place these macro variable references in the URL.  Finally, we simply need to tell PROC HTTP where to place the response from the call.  For this, we can simply place the file reference "imgfile" we created earlier.   And that's it!  Let's go ahead and run our code!     Well done!  We have now successfully created and saved an svg image of our Visual Analytics report tab.  And we did it in just 10 lines of SAS code!   Example 2: Snapshotting a Single Report Object   Now that we know how to create an image of an entire report, we can move on to creating an image of just a single report object.  Fortunately, the new Visual Analytics API makes this process as easy as the first example in this article.  In fact, we only need to add a single parameter to our PROC HTTP call.  The new parameter is: "reportObject".  For the value of this new parameter, we will need to retrieve the name of the object we want to import.  I have written a previous article on how to do this using SAS code.  However, you can also manually retrieve the report object’s name by opening your Visual Analytics report and clicking the three dots in the top right of the report object you would like to create an image of.  After clicking the three dots, select “Create link”.    In the example below, I'm selecting a bar-line graph in my Visual Analytics report:   After clicking "Copy link", a new window appears with a dynamically created link.  The very last bit of text in this link contains the report object's name.  In the example below the report's object name is: 've152':     From here we can execute a modified version of the code from the first example, but this time with our new parameter and associated parameter value.  As we did with the reportID, we create a reportObjectID macro variable and place the actual name of the report object we want to export into it.  You will also notice that we changed the name of the output image file to include the value saved in the reportObjectId macro variable.  This will make the output image that is place in our "My Folder" unique:   %let BASE_URI=%sysfunc(getoption(servicesbaseurl)); %let reportID = <- report uri ->; %let reportObjectId = ve152; %let height=800px; %let width=600px; filename imgfile filesrvc folderuri='/folders/folders/@myFolder' filename="myOutputImage_&reportObjectId..svg"; proc http method="GET" oauth_bearer=sas_services url="&BASE_URI/visualAnalytics/reports/&reportID/svg?size=&height,&width&reportObject=&reportObjectId." out=imgfile; run;   After running this code, we get a new image in our folder:     And that's it!  In just a few lines of code we have programmatically created svg images of both an entire Visual Analytics Report tab, and also an individual report object!   *The code in this article is intended to be run against VA reports which have been developed using the Visual Analytics interface using CAS Data sources that are ACTIVELY loaded into memory.   How to make this example work for you   On GitHub, you will find all the code discussed in this article.  All code is intended to be executed within a SAS Studio session running in a SAS Viya 2021.2.4 (or later) environment which contains the SAS Viya services that are being called.   On Github, you will find the following support files for this article: A SAS program which creates an SVG image file of an entire Visual Analytics Report Tab and saves the file in the private “My Folder” area for the user running the code - createVAFullReportImage.sas A SAS program which creates an SVG image file of a single report object that exists in a Visual Analytics Report and saves the file in the private “My Folder” area for the user running the code - createVAReportObjectImage.sas  Take Me to GitHub!   ... View more

In my previous article I demonstrated how to use the Job Execution Service Web Application to make a job request.  After the job completes running, its results are returned to the user interface in the form of an ODS HTML table.  One common question I get about creating applications using the Job Execution Service Web Application is if the job's results can be delivered in a different format.  The most popular request is if a JSON file can be returned.  Well, as it turns out it's very easy to do this!  As a matter of fact the Job Execution Service Web Application's documentation has a specific example which shows how to format a Job's results as a JSON file.   In this article, we will be building on the example I demonstrated in my previous article.  Specifically, we will add an additional prompt to the user interface asking the user if they would like to have the job's results returned as a JSON file or as a SAS ODS table.  If the user selects "JSON File" then we will use the concepts from the documentation's example to return the job's output as a JSON file.  After the JSON file is returned, some basic formatting will be applied to convert the JSON file's contents into a simple bulleted list.   The completed application will look like this:         This article will step you through how to build this example.  All code discussed is available in this GitHub Repository.   Let's get started!   Step 1: Conditionally changing what the Job's Source code returns   In order to edit the source code of our job, right click on the job and select "Edit" -> "Source Code":       The original example leveraged PROC REPORT to conditionally highlight the ODS table's cells depending on the state of the cas table.  For this example, we will keep it simple and only print the ODS table's output using PROC PRINT.  To do this simplification, all code beneath the step below can be removed:     /* run the getLoadedTables macro */ data _NULL_; set reportCaslibs; call execute('%getLoadedTables(' || trim(caslibEndpoint) || ')'); run; The next thing to do is to create a reporting table sourced from the output dataset which applies some formatted labels to the variables.  The code to do this is below:   data getReportDataSourcesRPT; set getReportDataSources; reportCasLib = upcase(reportCasLib); state = upcase(state); label reportCasLib="Caslib" reportCasTable="Table" state="State"; run; Finally, we can produce the output based on what the user has selected (aka the value of the reserved macro variable - "_output_type").  To do this, we will simply add some conditional macro logic.  If the value received is "JSON", then PROC JSON is used with webout (as demonstrated in the example) to create a json file.  Alternatively, if the value is "ods_html5", then PROC PRINT is used to create an ODS HTML table.  The code to do this is below:   /* generate the appropriate output */ /* user selected JSON as output - return output as json file */ %if %qtrim("&_output_type") = "json" %then %do; proc json out=_webout nosastags pretty; export getReportDataSourcesRPT; run; quit; %end; /* user selected ODS as output - return output as SAS ODS Table */ %if %qtrim("&_output_type") = "ods_html5" %then %do; proc print data=getReportDataSourcesRPT noobs label; label reportCasLib='CAS Library' reportCasTable='CAS Table' state='CAS Table State'; run; %end; And that's it!  Our Job's source code is ready to dynamically create the output the user has requested!  Next we will need to work on our user interface to prompt the user to make a output type selection, and then if JSON is requested format the output to a bulleted list.   Step 2: Adding an additional prompt to the user interface and formatting the response   We will first need to prompt our users to see if they would like the job results returned as a JSON file or a SAS ODS Table.  To do this we will need to edit the HTML for our JOB.  This can be achieved by right-clicking on the job and selecting "Edit"->"HTML form":        From here we can see the HTML code that we will be editing.  Directly above the <div id="reportList"> tag, place the following code:   </nbsp>Output Format: <select name="outputFormat" class="jobexec_sample_select"> <OPTION VALUE="JSON">JSON file <OPTION VALUE="ODS">SAS ODS Table </select> <p></p> Additionally, it would be nice if the job's output was left justified.  To do this, change the two "center" tags that contain the "JobResults" div tag to "left".   The complete HTML form and "JobResults" div tag should now look like this:     Now that our HTML dropdown is in place, we need to make it functional by editing our javascript function that submits the form data.  We will now be including a value telling us what the user has selected for the outputFormat prompt.  Depending on what has been selected, we will be setting the value of the reserved macro variable "_OUTPUT_TYPE" to either "JSON" or "ODS_HTML5" (ODS_HTML5 is the default).  To do this, navigate to the javascript function named "submitForm" and directly underneath the 'formData.append('_csrf', "$CSRF$");' line, add the following lines:   if (document.querySelector('[name="outputFormat"]').value == "JSON") { formData.append("_output_type", "json"); } else { formData.append("_output_type", "ods_html5"); }   If the user has selected "SAS ODS Table" the Job will return a nicely formatted HTML table and place it in the "JobResults" div tag.  However, if the user selects JSON file, we probably want to display something nicer than just the JSON text.  To do this, we will use some basic javascript to convert the returned JSON file to something that is more visually pleasing.  To keep this article's example very simple, we will convert the JSON to a bulleted list.  To do this, we will add some logic to the javascript function that displays the job's results.  Navigate to this function and update the "request.onreadystatechange = function ()" block to include the following:   request.onreadystatechange = function () { if (this.readyState == 4) { if (this.status == 200) { if (document.querySelector('[name="outputFormat"]').value == "JSON") { var jsonResponse = JSON.parse(this.responseText) console.log(jsonResponse) var i; var outputList = document.createElement('ul'); for(i = 0; i < jsonResponse.length; i++) { reportCaslib = "Caslib: "+jsonResponse[i]['reportCasLib'] reportCasTable = " || Table: "+jsonResponse[i]['reportCasTable']+" || State: " reportCasTableState = ""+jsonResponse[i]['state']; var listItem = document.createElement("li"); listItem.appendChild( document.createTextNode(reportCaslib) ); listItem.appendChild( document.createTextNode(reportCasTable) ); listItem.appendChild( document.createTextNode(reportCasTableState) ); outputList.appendChild(listItem) } document.getElementById("JobResults").innerHTML = "<h1 class='jobexec_sample_name'>Report's Data Source(s) - JSON file (formatted with Javascript):</h1>" + outputList.innerHTML; } else { document.getElementById("JobResults").innerHTML = "<h1 class='jobexec_sample_name'>Report's Data Source(s) - SAS ODS Table:</h1>" + this.responseText; } } else { document.getElementById("JobResults").innerHTML = "Status: " + this.status; } } };   Wow!  That's quite a change to the function!  Let's take a closer look at what it does.    First, we check to see if the user has selected "JSON" for their output.  If they have, we begin by parsing the response and output it to the console (outputting the response to the console is not necessary but has been included in this article's example for educational purposes).  After this we create a new html "ul" element and store it in a variable named "outputList".  After this, we loop through the JSON's contents.  On each pass of the loop, we capture the iteration's reportCaslib, reportCasTable and reportCasTableState's value and save them to variables with some formatting around them.  After this, we simply create a new html "li" element and store it in a variable named "listItem".  With these pieces in place, we simply append the reportCaslib, reportCasTable and reportCasTableState's values to the listItem.  To complete our loop, we take the final listItem variable and append it to the outputList.  Finally, we can output the results to the "jobResults" div tag.  For a more pleasant viewing experience, we will prefix the output with a nice title explaining that the user is viewing the results of the job as a JSON file (formatted with Javascript).     It should be stated that this javascript is very basic and there are much better ways of doing this formatting.  However, the purpose of this article is to show how to deliver different types of output in a Job Execution Service Web Application Job.  Hence the javascript in this example has been kept very elementary.   If the user has selected the SAS ODS Table option, the process is much simpler!  All we need to do is add our title prefix and output the results from the job.   Great!  We've converted our javascript function to process the JSON output (if that's what the user has selected) to a bulleted list.  As stated above, while this example is very basic, it could easily be improved upon.  For example, if the cas table's state is "UNLOADED", additional logic could be added (to both the javascript function as well as the SAS ODS table) to color the output text red.     And that's it, our HTML and Javascript is ready!  All that's left is for us to do some window dressing.  Let's change the title in our HTML to reflect what our app will do.  I decided to change the title to "This Example JES Web Application Job will Display Details About a VA Report’s Data Source(s) in different formats", but you can use any title you'd like!  Secondly, you might have noticed that we used the "jobexec_sample_select" class in our output type selection.  We should keep this consistent with our second report prompt.  Additionally, since we have some text in front of our output type prompt, we should also have some text in front of our second prompt.  To do both of these tasks locate the variable "prompt" within the createSelection javascript function and replace it with:   var prompt = 'Select Report: <select name="reportSelect" class="jobexec_sample_select">';   And that's it!  We have successfully added the needed components to first prompt the user to select their output format, and then return the appropriate results. By leveraging both the Job's source code as well as the HTML prompt we can really begin to shape our app to create any type of output we want:   *The code in this article is intended to be run against VA reports which have been developed using the Visual Analytics interface using loaded CAS data sets as the data source(s). Results might vary when selecting preinstalled VA reports, VA reports created via other methods or VA reports that have no data sources associated with them. How to make this example work for you   On GitHub, you will find all the code discussed in this article.  All code is intended to be saved in a SAS® Job Execution Web Application 2.2 job definition within a Viya 3.5 environment.    On Github, you will find the following support files for this article: A JSON file containing the completed JES Job - JES_selectOutputFormat.json *A SAS Administrator can import this to the Viya content folder "/Public" within a SAS Viya 3.5 (or later) environment using these instructions The JES job's source code that makes the API requests, runs the CAS actions and Viya Procedure and displays the ODS output - JES_selectOutputFormat.sas The JES job's HTML code to create prompts and execute the job - JES_selectOutputFormat.html  Take Me to GitHub!     ... View more

I'm a big fan of using Waterfall Charts in my SAS Visual Analytics reports.  In particular, I think they show very well when they are used to visualize time series data.  The only thing I don't like about them is you can only have one cumulative bar at the end of the graphic.  In fact, it would be really nice if a user could interactively choose where to display cumulative bars in their report's waterfall charts.    For example, a sales organization might want to know the subtotals of new sales after each quarter of the fiscal year.  Alternatively, someone looking at academic data would want to see the subtotals for their data at the end of the academic year (which might be in June).  This got me thinking, is it possible to give report users the great visual display of a waterfall chart, but also give them the functionality to interactively choose where they would like cumulative bars placed? Well, as it turns out, the answer is yes!  Using the SAS® Graph Builder, I was able to build the report below which has this functionality:     Pretty Neat!  The report above displays sales data sourced from the "prdsale" dataset available in the "SASHELP" library.  SAS also provides this dataset on the SAS Viya Example Data Sets website.  The user can first see a waterfall chart that has the standard Final (cumulative) bar at the end of the year.  However, if they wish, they can interactively select one or more months and additional cumulative bars appear!   In this post, I'll outline three steps to recreate this custom graph and report. Let's get started! Step 1: Build the graph   Start by creating a new custom graph using the SAS® Graph Builder. Drag the following objects onto the canvas in the following order: Waterfall Chart Bar Chart Next select the options menu on the left and from the drop down select "Bar Chart 1" and select the "Data labels" checkbox.       Still within the options menu, select "Waterfall Plot 1" and deselect the "Final (cumulative) bar" checkbox:       And you're done! You've successfully built the custom graph. Save your graph and give it a name. Step 2: Prepare your data As I mentioned above, for this article I will be using the prdsale dataset from the sashelp library.  The first thing we will want to do is summarize the sales figures in our data by Country, Year and Month.  PROC SQL is a great choice for doing this.  After the data has been summarized, we will also need to generate a Year to Date (YTD) column for the sales data we just summarized.  However, we must also ensure that the YTD column is calculated properly within its respective by-groups of Country and year.  For this, we'll dust off our handy data step skills!   The code to do this is below:   proc sql; create table sums as select Country, Year, Month,sum(actual) as MonthlySales format=dollar18.0 from sashelp.prdsale group by Country, Year, Month; quit; data sums2; set sums; by Country year month; if first.Country or first.Year then SalesYTD=0; SalesYTD + MonthlySales; format SalesYTD dollar18.0; run; A few observations from the output dataset are printed below:   With all our sales data in place, we will need to think about the structure of the graph's data.  The X axis of the graph shows both the "Month" and "Month YTD" values.  Hence, we will need to build a data structure that has both of these sets of categorical values in the within the same variable.  One way to do this is to create two copies of our working data set and generate the categorical variable with the appropriate values.  After this we can append the datasets back together.   The code to do this is below: data Monthly; set sums2; length Months $9; Months = put(Month,MONNAME3.); drop SalesYTD; run; data YTD; set sums2; length Months $9; Months = put(Month,MONNAME3.) || " - YTD"; drop MonthlySales; run; data dynamicTotalWaterfall; set YTD Monthly; run; A few observations from the "Monthly" dataset are printed below: And a few observations from the "YTD" dataset are printed below: And finally, a few observations from the "dynamicTotalWaterfall" dataset are printed below: Great! Our data is now ready for SAS Visual Analytics!   Step 3: Build the report! Begin by creating a new Visual Analytics report and adding the data set 'dynamicTotalWaterfall' to your report.  We are going to first create a parameter that will capture the date values that the user selects from the list control.  To do this, from the data menu on the left click "New data item" and choose "Parameter":    At the next screen name your parameter "dateParameter", select the checkbox for "Multiple Values" and press "OK": Before we add our new custom graph, lets add the list control to the report.  From the "Objects" menu, find the "Controls" section and drag the "List" object onto the reporting canvas.  Once it is there, select the "Roles" menu on the right, for the Category role select "Month" and for the Parameter role select "dateParameter":     We need to make one final edit to our list control.  From the "Options" menu on the right, scroll down until you see the "Required" checkbox and select it:   Since we created our YTD summary based on the subgroups Country and Year, we will need to require that the report user filter the graph by both of these variables.  Let's do this by adding two new dropdown controls as page controls to our report as required prompts:       Great!  Now our list control is ready and it's time to add our custom graph.  To do this, import the custom graph to the Visual Analytics report we're working on.  Drag the custom graph into the report's canvas and place it to the right of the list control we just created:   Great!  Now all we have to do is apply the proper roles to our custom graph.  Go to the "Roles" menu on the right pane and select your custom graph from the top dropdown.  Apply the roles as shown below:     What makes this graph dynamic is the user's ability to choose which month they would like to display the year to date sales.  This means, that the graph should always be showing all the "MonthlySales" data while also only showing the "SalesYTD" for the selected months in the list control.  To achieve this, we can add a custom filter to our graph.  With the custom graph selected, navigate to the Filters menu and click the "New filter" button and select "Advanced filter": At the next screen, we can create a filter that will meet the reporting needs.  First, we start with an expression which will filter out any records that have the string "YTD" in the "Months" variable.  This will ensure that all the "MonthlySales" data will be displayed.  Then we add an "OR" operator and within in add the logic to select any records that have the string "YTD" in the "Months" variable AND are currently in the "dateParameter" parameter (this is why we added the parameter to our list control earlier!).  The visual view of this filter is below:   And the text for this filter is below:   ( 'Months'n NotContains 'YTD' ) OR ( ( 'Months'n Contains 'YTD' ) AND ( 'Month'n In 'dateParameter'p ) )   Great!  We can now see our graph!  But wait, when we select a few months in our list control, something seems wrong... Of course!  The months are out of order!  Since we had to create a custom categorical variable for our x-axis, Visual Analytics is simply sorting its values alphabetically.  This can easily be fixed by creating a custom sort on our "Months" variable.  To do this, from the data menu on the left, right-click the "Months" variable and choose: "Custom Sort": At the next screen, order the values as they normally appear on the calendar year.  Place the values that have only the month abbreviation first, followed by the values that have both the month abbreviation with the " - YTD" after.   Press OK: And that's it! You have successfully built a Waterfall Chart that allows users to interactively choose where cumulative total bars are placed!  Additionally, you used this graph to visualize the sashelp.prdsale data in SAS Visual Analytics!  All that's left is for us to add some window dressing.  For this, I simply added a title to the list control to inform the user that a selection made there will result in subtotal bars being displayed. After this edit, our final report looks like this:     How to make this example work for you This example was created in SAS Visual Analytics 8.5.  For this report's data source, I used the "prdsale" dataset available in the "SASHELP" library.  SAS also provides this dataset on the SAS Viya Example Data Sets website.   On Github, you will find the following support files for this article: A JSON file containing the completed custom graph - dynamicTotalWaterfall_CG.json A JSON file containing the completed report - WaterfallChartWithDynamicSubtotals.json The code which creates the final dynamicTotalWaterfall data set - dynamicTotalWaterfallETL.sas  Take Me to GitHub!   Import the data on your SAS Visual Analytics instance.  Import the report via the "Import via GUI" section of these instructions.   ... View more

I love to make tally sheets.  Whether I'm trying to remember how many times it snowed over the past few winters or how many glasses of water I've drank each day of the week, there always seems to be a piece of paper in my pocket with a bunch of tally marks on it.  I recently began to wonder why I like making tally sheets so much.  It occurred to me that they are actually a great way to compare data by groups.    I then began to ask myself if I could build an interactive tally sheet in SAS Visual Analytics?  Well, as it turns out, the answer is yes!  Using the SAS® Graph Builder, I was able to build the report below which contains an interactive tally sheet comparing baseball player's total home runs:       Pretty Neat!  For this report's data source, I used the "baseball" dataset available in the "SASHELP" library.     Get the details of how to re-create this chart in this SAS Communities Library article. ... View more

I love to make tally sheets.  Whether I'm trying to remember how many times it snowed over the past few winters or how many glasses of water I've drank each day of the week, there always seems to be a piece of paper in my pocket with a bunch of tally marks on it.  I recently began to wonder why I like making tally sheets so much.  It occurred to me that they are actually a great way to compare data by groups.    I then began to ask myself if I could build an interactive tally sheet in SAS Visual Analytics?  Well, as it turns out, the answer is yes!  Using the SAS® Graph Builder, I was able to build the report below which contains an interactive tally sheet comparing baseball player's total home runs:     Pretty Neat!  For this report's data source, I used the "baseball" dataset available in the "SASHELP" library.  SAS also provides this dataset on the SAS Viya Example Data Sets website.  In this post, I'll outline three steps to recreate this custom graph and report. Let's get started!   Step 1: Build the graph   Start by creating a new custom graph using the SAS® Graph Builder. Drag the following objects onto the canvas in the following order: Vector Plot Scatter Plot Scatter Plot Next select the options menu on the left and from the drop down select "Vector Plot 1" and clear the "Show arrowheads" checkbox.  Also set the line width to be "6":     Still within the options menu, select "Scatter Plot 1" and set the Marker style size to be 5:     Still within the options menu, select "Scatter Plot 2" and make the same edits as you did to "Scatter Plot 1".   We will also need to make some axis edits. Select "X Axis" from the options menu drop down and clear all the check boxes. Also set the "Grid lines" option to be "Off":     Still within the options menu, select "Left Y Axis" and make the same edits of clearing all the check boxes and setting the "Grid lines" option to be "Off".   Continuing within the options menu, select "Legends" and set the "Visibility" option to "Hide All":     Now from the "Discrete Legend" section of the options menu, uncheck the "Scatter Plot 2", "Scatter Plot 1" and "Vector Plot 1" checkboxes:     The final edit we will make within the options menu is in the "A1" section.  Go to this section and for the "Overlaid Graph Colors" option, choose "Start each graph with its first default color":     Now that our graph options are set, we need to make some edits in the "Roles" menu.  Select this menu and start by adding a new "Data Driven Lattice Row":   At the next window, keep all default values and press "OK".   Under the "Shared Roles" section we will need to rename the shared role which was automatically created when we added our graph elements.  Click the three dots next to "Shared Role 1" and click "Edit Role":     Rename the role "X".  In the "Used by:" section click the "Unshare" button next to "Scatter Plot 2".   When you complete these edits, your screen should look like this:   Press "OK".   Now scroll down to the "Graphs" section of the Roles menu and find the "Vector Plot 1" roles.  Click the three dots next to the "Y" role and select "Create Shared Role With Another Role" -> "Scatter Plot 1 Y":   At the next screen, give this new shared role a name of "Y" and press "OK".   Still within the "Vector Plot 1" roles, click the three dots next to the "X Origin" role and select "Create Shared Role With Another Role" -> "Scatter Plot 2 X":   At the next screen, give this new shared role a name of "XOrig" and press "OK".   Still within the "Vector Plot 1" roles, click the three dots next to the "X Origin" role and select "Create Shared Role With Another Role" -> "Scatter Plot 2 Y":     At the next screen, give this new shared role a name of "YOrig" and press "OK".   And you're done! You've successfully built the custom graph. Save your graph and give it a name.   Step 2: Prepare your data   First we begin by creating a new dataset from the sashelp.baseball dataset.  For this example, we will only include the right fielders, so we'll begin with a where clause that returns only these players.  For our tally graph to work correctly, we will need each run a player scored to have its own observation.  Hence, we'll run a simple do loop from 1 to the player's total run count (contained in the variable "nHome").  Also, we will need to have a categorical column to use in the Lattice Row of our graph.  While we could simply use the player's name here, I have chosen to create a new column which contains the players total runs concatenated with the player name.  This will come in handy for sorting when we build our report.  The code to do this is below:   data baseballData; set sashelp.baseball; where Position = "RF"; runsPlayer = compress(put(nHome,z2.)) || " - " || name; label runsPlayer="Total Runs - Player Name"; do count=1 to nHome; output; end; keep Name nHome count runsPlayer; run;   A few observations from the output dataset are printed below:     The next step is to create coordinate data points for the x and y positions for each of the tally marks. This is very similar to how an annotate data set is used when creating ODS graphs.  In order to generate these positions, we will join our data set 'baseballData' to the "tallyTemplate" data set which is in this post's supporting GitHub Repository.  The join will be based on the "count" variable.  In order to generate a nicely formatted dataset, we will order the output dataset by name and count.  The code to do this is below:   proc sql; create table baseballTallyChart as select t1.*, t2.x, t2.xOrig, t2.y, t2.yOrig from baseballData as t1 left join tallyTemplate as t2 on (t1.count = t2.count) order by name, count; quit; A few observations from the output dataset are printed below:     Great! Our data is now ready for SAS Visual Analytics!   Step 3: Build the report!   Begin by creating a new Visual Analytics report and adding the data set 'baseballTallyChart' to your report.  We will first need to adjust the aggregation for the column "Home Runs in 1986" (aka "nHome").  Expand this column and set the aggregation to "Average":     Before we add our new custom graph, lets add the list control to the report.  From the "Objects" menu, find the "Controls" section and drag the "List" object onto the reporting canvas.  Once it is there, select the "Roles" menu on the right and for the Category role select "Player's Name" and for the Measure role select "Home Runs in 1986":     It would be nice if the list control shows the player who scored the most home runs at the top!  So lets sort the control in this way.  Right click on the list control you just added and select "Sort" -> "Home Runs in 1986: Descending".     We only need to make one final edit to our list control.  From the "Options" menu on the right, scroll down until you see the "Required" checkbox and select it:       Great!  Now our list control is ready and it's time to add our custom graph.  To do this, import the custom graph to the Visual Analytics report we're working on.  Drag the custom graph into the report's canvas and place it to the right of the list control we just created:     We will need to set up the report so when a user clicks one or more players in the list control, it filters the custom graph.  To do this, select the list control and go to the "Actions" menu on the right.  Select the checkbox next to your custom graph:    Great!  Now all we have to do is apply the proper roles to our custom graph.  Go to the "Roles" menu on the right pane and select your custom graph from the top dropdown.  Apply the roles as shown below:     Now our roles have been assigned and we can see our graph!  All that's left is for us to add some window dressing.  I modified the width of the list control as well as the custom graph until the report looked the way I wanted.  I also added a title to the report's tab.  Also, for the initial view of the report, I decided to choose 5 players from the list control (however you can choose any number you like) and to color the tally marks red.  To do this, with the graph selected go to the options menu and find the "Line/Marker" color palette.  Change the first color to red:       The final step is to sort our custom graph so the player who hit the most home runs is at the top!  To do this, right click on the custom graph and choose "Sort" -> "Total Runs - Player Name: Descending":     And that's it! You have successfully built the Tally Sheet Custom Graph and used it to visualize the sashelp.baseball data in SAS Visual Analytics!   How to make this example work for you This example was created in SAS Visual Analytics 8.5.  For this report's data source, I used the "baseball" dataset available in the "SASHELP" library.  SAS also provides this dataset on the SAS Viya Example Data Sets website.   On Github, you will find the following support files for this article: A JSON file containing the completed custom graph - tallyChartGC.json A JSON file containing the completed report - tallySheet.json The template dataset for creating the tally marks - tallytemplate.sas7bdat The code which creates the final baseballTallyChart data set - tallySheetETL.sas  Take Me to GitHub!   Import the data on your SAS Visual Analytics instance.  Import the report via the "Import via GUI" section of these instructions.   ... View more

In my last article I demonstrated how to use the Job Execution Web Application Service to create an application that leverages both the Viya APIs and CAS actions to retrieve information about a Visual Analytics report's data source(s).  But did you know that JES developers can also make use of the Viya procedures?  In this article, we will be using a Viya procedure to add additional functionality to the application I created in my previous article.  Specifically, we will add an additional prompt to the user interface asking the user if they would like to retrieve cardinality information for the report's datasets that are currently loaded into memory.  If the user chooses to do this, the program will then run PROC CARDINALITY and return the results at the bottom of the output!  The completed application will look like this:   Pretty sweet!  Let's take a closer look at what is creating the job's output.  The information retrieved using the Viya APIs is highlighted in ORANGE, the information retrieved using CAS Actions is highlighted in BLUE and finally information retrieved using a Viya Procedure is highlighted in PURPLE: The example above demonstrates yet another way developers can harness the power of the Viya platform (Viya APIs, CAS Actions and Viya Procedures) to create applications using the Job Execution Web Application Service!   This article will step you through how to build this JES example.  All code discussed is available in this GitHub Repository.   Let's get started! Step 1: Adding an additional prompt to the user interface   We will first need to prompt our users asking if they would like to run PROC CARDINALITY on any datasets that are currently loaded into memory.  To do this we will need to edit the HTML for our JOB.  This can be achieved by right-clicking on the job and selecting "Edit"->"HTML form":     From here we can see the HTML code that we will be editing.  Within the HTML form in our code there are two </nbsp> tags.  In between them, place the following code:   <br><input type="checkbox" id="getCard" class="jobexec_sample_input_checkbox"> Run PROC CARDINALITY on loaded data set(s) The complete HTML form should now look like this:   Now that our HTML checkbox is in place, we need to make it functional by editing our javascript function that submits the form data.  We will now be including a value telling us if the box has been checked or not.  To do this, navigate to the javascript function named "submitForm" and directly underneath the first 'formData.append' line, add the following lines:   if (document.querySelector('#getCard').checked == true) { formData.append("cardRequest","true") } else { formData.append("cardRequest","false") }; This will pass a new macro variable named "cardRequest" to the Job Execution WebApp's SAS code.  The macro variable's values will be either "true" (if the user has checked the box) or "false" (if the user left the box unchecked).   The first few lines of the submitForm function should now look like this:   And that's it for editing the HTML form!  Next, we need to edit our SAS program to run the PROC CARDINALITY procedure!   Step 2: Adding PROC CARDINALITY to the Job's Source Code   In order to edit the source code of our Job Execution WebApp Job, right click on the job and select "Edit" -> "Source Code":   At the very bottom of the SAS code, add the following lines:   %if &cardRequest = true %then %do; %macro retrieveCardinality(caslib,casdata); %let maxLev=254; proc cardinality data=&caslib..&casdata outcard=casuser.card maxlevels=&maxLev; run; PROC SQL noprint; select max(_cardinality_) into :maxCard from casuser.card; quit; footnote; %if &maxCard = &maxLev %then %do; footnote ITALIC HEIGHT=2 "*PROC Cardinality's MAXLEVEL set to: &maxCard"; %end; title "Cardinality For &caslib..&casdata"; proc print data=casuser.card label noobs; var _varname_ _type_ _cardinality_; run; title; %mend retrieveCardinality; cas myses; caslib _ALL_ assign; data getReportDataSourcesCard; set getReportDataSources; where state="loaded"; if upcase(compress(scan(reportCasLib,1,"(")) = "CASUSER") then reportCaslib="CASUSER"; else reportCaslib=reportCaslib; call execute('%nrstr(%retrieveCardinality(' || compress(reportCaslib) || ',' ||compress(reportCasTable) || "));"); run; cas myses terminate; %end; The code above does a few tasks.  Let's go through it step by step.   First there is a "%if" check to see if the macro variable "getCard" value is equal to "true", and if so there is subsequently a "%then %do" block of code to be executed.  Within that block of code, we first create a macro named "retrieveCardinality" to execute PROC CARDINALITY dynamically.  Input parameters for the macro are a caslib and a cas dataset (named "caslib" and "casdata" respectively).  The macro first executes the proc, feeding it the caslib and cas dataset that are in the input parameters.  The data table to contain the cardinality information for each variable is saved to a cas table in the user's personal caslib named "card".  We have also set the maxlevels value to be 254 (which the documentation tells us is the max value we can request).  Since users of our application might not know that the maxlevels option is set to 254, it would be good to place a footnote at the bottom of the table in the event that some of the variables' cardinality is greater than 254.  To do this, we run PROC SQL on the table "casuser.card" and retrieve the max value of the variable "_cardinality_".  We record this value in the macro variable "maxCard".  Finally, we check to see if this value is greater than 254; and if so, we conditionally print a footnote informing the user that the maxCard limit has been reached. After PROC CARDINALITY has completed, we simply print out the results of the table casuser.card using PROC PRINT.  Since the macro will interact with CAS we first establish a cas session and assign our libraries.  With our macro created and our CAS session established, we now will need to execute the retrieveCardinality macro dynamically. As we saw in the previous example, the source code of this Job Execution WebApp Service Job leverages one main dataset named "getReportDataSources" which contains all the datasets that the VA report uses.  We subset this dataset to contain only the loaded cas tables and then use this dataset in combination with a CALL EXECUTE Routine to run our macro dynamically on the resulting tables.  When all is completed, we terminate our CAS session.   And that's it!  We have successfully added the needed components to first prompt the user to see if they want to run PROC CARDINALITY, and if so run the procedure and return the results. By leveraging this additional functionality along with the results from the API and CAS Actions from the previous example, we have built a powerful little application that returns a lot of useful information about a Visual Analytics Report's Datasets:       *The code in this article is intended to be run against VA reports which have been developed using the Visual Analytics interface using loaded CAS data sets as the data source(s). Results might vary when selecting preinstalled VA reports or VA reports created via other methods.   How to make this example work for you   On GitHub, you will find all the code discussed in this article.  All code is intended to be saved in a SAS® Job Execution Web Application 2.2 job definition within a Viya 3.5 environment.    On Github, you will find the following support files for this article: A JSON file containing the completed JES Job - JES_APIActionProc.json *A SAS Administrator can import this to the Viya content folder "/Public" within a SAS Viya 3.5 (or later) environment using these instructions The JES job's source code that makes the API requests, runs the CAS actions and Viya Procedure and displays the ODS output - JES_APIActionProc.sas The JES job's HTML code to create prompts and execute the job - JES_APIActionProc.html  Take Me to GitHub!   ... View more

My favorite time of year is fast approaching...football season! I love everything about American football, both college and the NFL. The two teams I support are the Carolina Panthers and the NC State Wolfpack. Throughout the season, if either team is playing at home, you will find me cheering them on in each team's respective stadium. When the game schedules are released, I'm always curious as to when the "Big Weekends" are (the weekends where I go to both a Panthers and NC State home game).     This year I decided to use SAS Visual Analytics to build a visualization that displays both the NC State and the Panther's home games. Doing so allows me to easily pick out the "Big Weekends." A schedule chart is an ideal choice to show this.   Built Schedule Chart The built schedule chart that comes with SAS Visual Analytics Designer has the ability to display events of varying duration that overlap across time. However, there is one limitation when it comes to using the schedule chart: it can't be used as the source of interactions. This means that you can't select a particular segment of time and use that selection to brush or filter another object.   For example, in the dashboard below, I have used the out of the box schedule chart to show only the home games for the two teams. The table on the right shows the full schedule of home and away games.       I would like to be able to click a time segment in the schedule chart and have the the table filter show me the details for the game I have selected. The schedule chart does not allow this (even when a group role is assigned). When I'm in the interactions view, the option to use the schedule chart as the source of an interaction isn't available (the pencil doesn't appear when I roll my mouse over the object).     Building a Custom Graph Fortunately, we can build our own custom graph that looks almost identical to the out of the box schedule chart and have this feature enabled! Below, I explain how to make this plot using the SAS Visual Analytics Graph Builder as part of the community custom graph series.   It's fairly easy once you start using the VA Graph Builder from the right panel in VA Home (look for Custom Graph) or from the object tab in the left panel in Visual Analytics Designer.     In Visual Analytics 7.3, there is a Vector Plot available in the New Custom Graph gallery. From this gallery that is on the initial page, scroll down and select the Vector Plot graph.     Edit Roles Once you have selected this plot, the next step is to edit the roles that come with the stock Vector Plot template. First, we will remove the two Data-Driven Lattice roles. This can be achieved by clicking the small arrow to the right of these roles and selecting Delete Role.     Do this for both the Column and Row Roles under the Data Driven Lattice menu.   Next, continue to edit the roles that come with the stock Vector Plot. These edits will enable the plot to show the data the way we want. First, edit the X role under Vector Plot 1. This can be achieved by clicking the small arrow to the right of the role and selecting Edit Role. After clicking Edit Role you will be presented with a dialog box asking how you wish to edit the role. For this role, we want to rename it "Week End" and change the classification to Datetime.     Click OK. You will then be presented with a dialog box asking you to confirm that both the X and X Origin classifications will now be Datetime. Click OK.   Using the same method, change the Y role under Vector Plot 1 be Datetime. You will also confirm you want the Y Origin classification to be Datetime.   You should also rename all the roles to be more appropriate for the data we are going to feed the visualization. This can be done via the same method for how we renamed the X role. However, we do NOT need to change the classification. For each role, simply select Edit Role and edit the names as below:   X = Week End Y = Game Day X Origin = Week Start Y Origin = Game Day Group = Location We now need to share two of the roles. The two roles we will share are: Game Day (originally Y) and Game Day (originally Y Origin). This can be achieved by clicking the small arrow to the right of the Y Origin (now called "Game Day") role and selecting "Create Shared Role With Another Role". Select the "Game Day" Role. You will be presented with a dialog screen of what to name your new shared role. Name it "Game Day" and leave the classification as Datetime.   Edit Properties We will now need to edit some of the properties of our new custom graph. Click the "Properties" tab on the upper right of the screen. From the main drop down at the top, select "Vector Plot 1".     The properties for the Vector Plot will be displayed. From here, uncheck the box "Show arrowheads".       Next, from the top drop down on the "Properties Tab" select "Left Y Axis".     Under the Left Y Axis Properties, check the option "Reverse Order".   Now you've completed your custom graph! Click File -> Save and save your graph with an appropriate name. I chose to name mine "Football Home Schedules".   Use Graph in a Report The final step is to use your graph in a report. For the data, you can use the data named "Football_Season_2016.csv" that is attached to this post as a csv file. Be sure this data is available in SAS Visual Analytics Designer. Place your newly created custom graph on the report canvas and map the data to the graph as shown in the Roles tab on the right of the screen shot below:     Edit Custom Graph Properties Next, we will need to edit the properties of our new custom graph. Select the "Properties" tab uncheck the options "Show grid lines" and "Show legend".     The last step is to color the segments on the plot to the appropriate colors of the Wolfpack and Panthers. In order to do this, we will create a display rule. If you have never created a display rule before, instructions on how to do so can be found here (under the "Add Display Rules to a Graph Using Color-Mapped Values" section).   Create a display rule for your schedule chart which matches the screen shot below:     If you're having trouble getting the colors for the Wolfpack, Panthers, or other values correct, here are the HTML codes for them:   Wolfpack - CC0000 Panthers - 0088CE Away - FFFFFF NONE (Bye Week) - FFFFFF   Edit Graph Styles The final step to make our custom graph really look like the out of the box schedule chart is to edit the styles of the graph. Select the "Styles" tab and for "Line Thickness" select "6".     Add Table Your custom schedule chart is now working. Now, all we have to do is add the table. From the "Objects" tab, under the "Tables" section, drag a table to the left of the graph already on the reporting canvas:     For your new table object, add the following variables in this order:  Team Game Date Opponent Location Once added, sort the table by the "Game Date" variable.   Interactive Schedule Chart The best part is next. Since we have built our own schedule chart, we can use it as the source of interactions. Click the interactions tab. Roll your mouse over the graph object and you will see the pencil. This means that the desired interaction is now possible!  Click and drag the pencil toward the list table and create a filter interaction.   Click "Close" to exit the interactions view.   Now your two objects will interact. You've built an interactive schedule chart that can filter the table! Below I have selected an NC State home game in the graph and the table shows me the details of the game: October 1st, against Wake Forest in Raleigh.     Pretty cool!  If you like, you can continue to label the objects as you see fit. I added the following labels:   Graph - "2016 Home Football Schedules" Table - "2016 Full Schedules" Use the Report to Visualize the "Big Weekends" In the screen shot below, I've opened the report in SAS Visual Analytics Viewer. I have selected all the weekends that have both a Panthers and NC State home game (once selected, the line turns grey). The graph shows me visually when the "Big Weekends" are and the table tells me the details (game date and opponent).     Looks like there's going to be a total of 4 "Big Weekends" this year!  Can't wait!  GO Pack!  GO Panthers!   How to Make the Example Work for You This example was created in SAS Visual Analytics 7.3. Attached is a SAS package file containing the sample report and custom graph.  Also, as mentioned before, the data in csv file format is also attached. Load the csv file into memory on your Visual Analytics instance and you will be able to import this package if you have access to the SAS Management Console 7.3. Please refer to these instructions.     If you do not have access, contact your SAS Visual Analytics Administrator. Be sure to select the report when importing.               ... View more

In my last post, I demonstrated how developers can use java script within a JES’s HTML input form to control how job output is handled.  This was useful because it allows both the user prompt and JES output to be displayed on the same page.  However this is really just the tip of the iceberg when it comes to all the ways developers can use the JES HTML input form to create dynamic, useful applications for report consumers.    In this post, I will build on the previous JES example by incorporating request calls to the Viya Reports API.  Just like in the previous example, when the job is called, it first shows some custom HTML.  Within this HTML page we will make XMLHttpRequest request to the Viya Reports API to get a list of the ten most recently created Visual Analytics reports in the Viya environment.  We will use this list to populate an HTML drop down from which the user can make a selection.  After the user makes their selection it will be passed to the job's SAS code and will make another request to the Viya Reports API to get the visual elements that are contained within the report.  Finally, we will display a table of only the Graph type elements to the user.   A view of what the finished example looks like is below:     Pretty neat!  In this post, I'll show you how to build this example!  Let’s get started!   Step 1: Create the JES Job and Adding Source Code Before we can create our HTML input form, we will need to create our JES job and add some source code to it.  We'll start within the Job Execution WebApp by creating a new job.  In the screenshot below, I'm creating a job in the folder "/Public/JES_Example"     Give your job a name.  In my example, the job is named "JESPromptsOutputViyaAPI". All other options can remain as their default values.  Press "OK":       Now we need some SAS code for our job to execute. You can add this by right-clicking the job and selecting: "Edit" -> "Source code":         From here you can add SAS Code to the job and save it.   To start my code, I'm adding SAS code which uses PROC HTTP to make an API call to the Viya Reports API and gets a report's content elements.  Notice that there is a SAS macro variable "&JESParameter" which will eventually contain a specific report uri when the code is run:     *if you are unfamiliar with how to use SAS to make requests to the Viya API, check out my previous article which goes into further explanation of how to do this.   Now that we have made our call, we can use the json libname engine to read in the response and return the visual elements from the report.  For this example, we are only going to create a report which lists the visual elements that are of type "Graph".  Hence, we will create a data set that only contains these elements:     Finally we want to create our output report.  We will use proc report to list any graph elements that our report has in it.  However, we will also need to accommodate for the event that the report has no graph elements (i.e. the report might only have a list table in it).  Hence we will first check how many observations are in the 'reportElements' data set.  If the observation count is greater than zero, then we will run our proc report step.  If the observation count is equal to zero, we will generate a message informing the user that the report they selected has no graph elements:     Great!  We've added all the needed SAS code to our job.   Step 2: Use the Viya Reports API to create an HTML prompt   As I mentioned in my previous article, one of the best parts of the Job Execution WebApp is the ability to create custom HTML containing input form(s) that can be displayed before the job's SAS code runs.  However did you know you can place code in these HTML forms that can make calls to the Viya API?  This can be very helpful if you want to populate your custom HTML with information about the Viya system.    For example, perhaps I wanted to create a drop down selection which allows the user to choose a from the 10 most recently created Visual Analytics reports on our Viya environment.  Well it turns our that this is super easy when we are working with the Job Execution Web Application.  All we need to do is make a "get" request to the Viya Reports API and get a collection of reports.  We also want to ensure that we return the most recently created Visual Analytics reports, so we will use the sorting functionality provided by the Viya API platform.  Finally we only want to return ten reports.  So we will leverage a pagination query parameter to our request to limit the number of items the API returns.    While this might sound like a complicated API request, it's actually quite simple if you look at the request call:   GET /reports/reports?limit=10&sortBy=creationTimeStamp:descending   Now that we have the request syntax, we can place it in the custom HTML for our job.  To do this, right-click the job and select: "Edit" -> "New HTML Form":       Here is where you can add any HTML that you would like to display to the user before the job is submitted. As in my previous article, I will be using code sourced from the Executing a Job Using JavaScript - Sending Small Data to the Job example in the Job Execution Web Application Service 2.2 Documentation.  I will modify this code slightly by creating a drop down prompt from the response from Viya Reports API and placing it in the 'reportList' DIV tag within page's <body> tag.  When the user makes a selection, it will then be passed into the job's SAS Code as a macro variable. The SAS code executes, and its output is returned and placed in the 'JobResults' DIV element within the HTML Form's <body> tag. Let's begin by adding basic HTML page with a user selection:       Great! Now that our user form is in place, we will need to add the script which will create our HTML dropdown prompt.  This will happen in two steps.  First we will make the request to get the ten most recently created Visual Analytics Reports.  Second we will loop though each item in the API's response json file and iteratively convert it into drop down list options.  The code to do this is below:       With this code in place, now's our first chance to see our work in action!  To do this, right click on your job and choose: "Display form" -> "HTML form":       We can see our script has made the call to the reports service, converted the response into a drop down selection, and inserted it into the HTML.        Looks like I have quite a few Visual Analytics reports which are still in development!   The final step is to add one final script which will take the user's selection and submit it to the job using the POST method.  If you are unsure about how the following steps work, they are covered in detail in my previous article.     Note use of the value $PROGRAM$ as the value for the _program input tag. As the JES documentation points out, "When the HTML input form is displayed, the path and name of the program to execute are substituted for $PROGRAM$."  This makes the HTML code we are writing more portable.    Great!  You're done with the coding part of creating the example.  All that's left is for us to add a job definition parameter so the HTML input form will be displayed before the job is executed. This is easily done by right-clicking the job and selecting: "Properties":         From here, select "Parameters".  Click "Add a new parameter".  Apply the following values:   Name: "_action" Default value: "form,execute" Press Save:     And you're done!  To see your completed work, simply select your job and click the "Submit" button.  From there make a selection from the HTML prompt we created, press submit, and see the results of the JES job all on the same page!  Here is what my finished JES output looks like:         Notice both API calls are shown.  First the call to the Viya Reports API to create the selection (highlighted in red), second selection is passed the selection to the JES job itself (highlighted in black) and finally the SAS code which makes a call to the Viya Reports API to return the report's graph elements is highlighted in green!   The example javascript and SAS code used in this example are very simple in order to demonstrate the basic principles of how each piece of the job work together. However these concepts can be used as building blocks to make the job much more exciting. More elaborate API Calls, HTML prompts, javascript code and SAS programs can be added to truly make a JES job that can meet report user's needs.   How to make this example work for you   On GitHub, you will find all the code discussed in this article.  All code intended to be saved in a SAS® Job Execution Web Application 2.2 job definition within a Viya 3.5 environment.    On Github, you will find the following support files for this article: A JSON file containing the completed JES Job - JESPromptsOutputViyaAPI.json *A SAS Administrator can import this to the Viya content folder "/Public" within a SAS Viya 3.5 (or later) environment using these instructions The JES job's SAS source code to create the ODS output - JESPromptsOutputViyaAPI.sas The JES job's HTML code to create prompts and execute the job - JESPromptsOutputViyaAPI.html  Take Me to GitHub!           ... View more

The world is a big place!  And sometimes creating Visual Analytics reports that have detailed coordinates on a geo map can be challenging because your source data doesn't contain known geography columns from which you can a create a hierarchy.  If this is the case, when a report viewer wants to review a specific area of the map they will be forced to zoom and zoom and zoom in on the map for quite some time before they can finally view the data they are interested in.    For example, imagine we have the following data containing the longitude and latitude for a set of 805 individual US zip codes:     We want to visualize this in map as part of a Visual Analytics report.  Unfortunately, if we simply map these zip codes on a map, they are so tightly clustered that a user must zoom in quite a bit to view the individual data points.  So if the user is interested in the data located in Boston, Massachusetts it will take quite a bit of zooming in until they can see it:     This is less than ideal.  What would be great if the data contained some additional higher level geographic columns.  If it did we could create a hierarchy so the user could simply "Drill Down" on the map in just two clicks:     The drill down method for looking at the data near Boston is certainly the better way to allow our users to navigate the map.   Even though these additional columns aren't in our source data, that doesn't mean we can't use SAS to build them!  Because we have the full longitude and latitude for each zip code in our source data, we can use this to generate the additional columns needed for our hierarchy. This page explains how the various decimal places of a longitude and latitude coordinates represent different levels of geographic precision.  We can see that coordinates with decimal degrees of 0.1 represent a "Large City or District" and coordinates with decimal degrees of 0.001 represent a "Neighborhood or Street" and so on.  So all we need to do is create these levels of geographic precision by rounding our current longitude and latitude columns.  In this article, I'll show you how to do this!   Let's get started!     Step 1: Create your hierarchy   Let's begin by generating the sample data for our example.  As I mentioned before, the source data for the report shown above contains the longitude and latitude for a set of 805 individual US zip codes.  I created this data from the sashelp.zipcode data set.  To recreate the example source data, first we want to isolate the individual coordinates for each zip code.  We will also want to convert the zip code to a character variable so we can use it as a geography data type in our report.  This is easily done via the PROC SQL statement below:   proc sql; create table getZips as select put(zip,z5.) as zipcode, x, y from sashelp.zipcode; quit; A few observations from the output data set getZips are shown below:       This three column data set will be the starting data for our example.  Now that we have the our zip codes with their respective coordinates we can begin the process of creating our hierarchy of varying levels of precision using the latitude and longitude variables.  What we need to do is take each observation and round the x and y columns to the level of precision we want.  After that, we will need to create a character variable that can serve as the category column for the geography data level.  Finally, we will add a label to our new category column.    Since we want to repeat this process for each level of precision, I have wrapped the steps listed above into a macro named "buildHierarchy".  When I call the macro, I'm using it to create a hierarchy with the levels of precision at:   Large City or District - 0.1 Town or Village - 0.01 Neighborhood or Street - 0.001   data buildHierarchy; set getZips; %macro buildHierarchy(level,prec,var,label); lat_&var = round(y,&prec.); long_&var = round(x,&prec.); &var = "&var: Lat:" || compress(lat_&var.) || " x " || "Lon: " || compress(long_&var.); label &var = "&label"; %mend; %buildHierarchy(00,.1,City,Large City or District) %buildHierarchy(01,.01,Town,Town or Village) %buildHierarchy(02,.001,Neighborhood,Neighborhood or Street) ; run; A few observations from the output data set buildHierarchy are shown below:     For this article, I've chosen to use observations where the frequency of the "city" variable is 35 or more.  This step is completely optional, however if you would like to subset your data as I did, run the PROC SQL and DATA STEP code below:   proc sql; create table getTopCities as select *, count(City) as totalCityObs from buildhierarchy group by City having (calculated totalCityObs) > 35; quit; data buildhierarchy; set getTopCities; drop totalCityObs; run; Great!  Our data is now ready for SAS Visual Analytics!   Step 2: Build the report!   Begin by creating a new Visual Analytics report and add the data set 'buildhierarchy' to your report.   We will first need to convert our character columns into geographies.  Let's start with the "Large City or District" column.  From the data menu select this column and change its category to "Geography":     At the next screen, for "Geography data:" select "Latitude and longitude in data".  For the Latitude and Longitude mappings choose:   Latitude (y): lat_City Longitude (x): long_City Press "OK".     Great!  Now repeat the process for the other categorical columns.  Use the latitude and longitude mappings below:   Town or Village Latitude (y): lat_Town Longitude (x): long_Town Neighborhood or Street Latitude (y): lat_Neighborhood Longitude (x): long_Neighborhood zipcode Latitude (y): Latitude (degrees) of the center (centroid) of ZIP Code Longitude (x): Longitude (degrees) of the center (centroid) of ZIP Code. With our geography data items in place, we can now create our hierarchy.  From the data menu, click "New data item" and select "Hierarchy":     Name your hierarchy "CustomGeoHierarchy" and add the columns in the order shown below.   Press OK:   *Notice I have chosen not to use the "Neighborhood or Street" column in my hierarchy.  This is simply a design choice I made.  You can include it in your hierarchy if you like.   Great!  Now we can build our map!  Drag your new hierarchy onto the reporting canvas.  Since the hierarchy is made up of geographic data items a GeoMap report object will be automatically created:     By default, Visual Analytics 8.5 will create a coordinates map for you.  Since we want to point out which areas have the most zip codes we will need to change this to a Bubble Geo Map.  To do this, click the map object and select the "Options" menu on the right handed side of the screen.  From within this menu, scroll down until you see the "Coordinate" section and select "Bubble":     Great!  All that's left is to do some window dressing.  I decided to give my map a title of "Custom GeoHierarchy".  I have also removed the legend.   With these final edits in place we have successfully created our report!  Now that we created our custom geographic columns in SAS and used them in a hierarchy in Visual Analytics, a user can drill down to detailed data in just two clicks!     This example was created in SAS Visual Analytics 8.5.  However the concepts from this article will work in any version of SAS Visual Analytics.  Also, if you are using SAS Visual Analytics 8.5 you could also consider using the "Cluster adjacent markers" option within a coordinate geo map.  You can learn more about this options in the SAS Visual Analytics 8.5 documentation.   How to make this example work for you   This example was created in SAS Visual Analytics 8.5.  Again, for this post's data, I used the zipcode data set which is available in the sashelp library.   The attachments for this post are: The ETL which creates the final buildhierarchy.sas7bdat dataset - buildhierarchy.sas         ... View more

As we make the transition from summer into fall, I've been seeing lots of news stories describing all the storms that are occurring.  Be it hurricanes, thunderstorms, or just heavy rain it seems that this time of year has a lot of weather events.  This got me thinking, what is the distribution of total weather events throughout the year?  Are there more rainstorms in spring or fall?  To answer this question I decided to create a nested bar chart using the SAS® Graph Builder in SAS Viya.    In just a short time, I was able to create the report below:     Very interesting!  The custom graph allows us to see which quarter (or season) of the year specific storm events happen.  More so, since the visual is a nested bar chart, we have the ability to see the number of events for each quarter as a proportion of the total events for the year.    The data source for the example report above is obtained from the SAS® Viya® Example Data Sets webpage and processed with with SAS Visual Analytics 8.5.   Get the details of how to re-create this chart in this SAS Communities Library article. ... View more